SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

learn more… | top users | synonyms

0
votes
3answers
84 views

Select data from single table using Self join

I have requirement to select a data from single table. The requirement is as follows Select data from table which has one value but not another. for e.g I want to select distinct ID which has value 1 ...
0
votes
2answers
72 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
0
votes
1answer
63 views

get all the count of sub category from the product table in single query with mysql optimization [closed]

Table structure: CREATE TABLE some_table ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `Product_model` varchar(255) NOT NULL, `product_discount` ...
0
votes
1answer
52 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
0
votes
0answers
23 views

Is it possible to stack similar columns from a subquery and join the other unique columns in one query?

I am fairly new to the sql world and have been struggling with a query. I have two subqueries that are generating tables with two columns. The first table is part # and model_countH. The second table ...
2
votes
2answers
94 views

Query with WHERE NOT and NOT IN with subquery

MySQL Version: 5.6.27 I am using the following two tables TABLE: oc_product product_id int(11) model varchar(64) tax_class_id int(11) and TABLE: oc_product_to_category ...
1
vote
1answer
60 views

Single MySQL query with row averages based on conditions

I have problem, where i know how to proceed with multiple queries, but dont really now I Have a table with BP { name: varchar systolic : int diastolic : int timestamp: date } I need ...
1
vote
2answers
91 views

simple SELECT EXISTS takes about two hours to execute

I have mysql server hosted. Now, If I issue simple SQL statement SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND ...
0
votes
1answer
81 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly ...
4
votes
2answers
67 views

Join subquery by year and month

I want to collect data from differed tables grouped by year and month. I'm using subquery for each sub data. Using mysql. SELECT YEAR(td.db_date) AS stat_year, job.total_jobs FROM ...
1
vote
1answer
44 views

Return the lowest value

How do I go about getting the course number (CNO) of all the lowest enrolled by department? For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it ...
0
votes
3answers
103 views

Updating rows from other rows in a huge table

I have a huge table with items item-id | name | type | extra Many of them are "old versions" of type 0. I have to update the extra field in all new items (same item-id, but type > 0) with the ...
2
votes
1answer
94 views

MariaDB subquery cache scope and settings

I am migrating an application from MySQL 5.6 to MariaDB 10. One major new feature of MariaDB is the subquery cache. I am trying to determine the expected effect this cache will have on our ...
0
votes
0answers
96 views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
1
vote
1answer
50 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
0
votes
0answers
36 views

MySQL - SELECT Columns from results of another Query [duplicate]

I'm trying to use a dynamic range of columns fetched from another query, specifically I want the results of this query: SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns WHERE ...
15
votes
3answers
413 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
1
vote
2answers
133 views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
8
votes
1answer
144 views

How can I get the hierarchical values from the below query?

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID. I need to get all the category IDs and their subcategory ...
2
votes
1answer
39 views

Problem to select not in

I want to display the values that are between 0 and 10 and dont exist in the current table For example : Field 1 2 3 5 9 8 Result expected: 7 4 The problem is that the column field is a ...
2
votes
2answers
296 views

PostgreSQL query very slow when subquery added

I have a relatively simple query on a table with 1.5M rows: SELECT mtid FROM publication WHERE mtid IN (9762715) OR last_modifier=21321 LIMIT 5000; EXPLAIN ANALYZE output: Limit ...
3
votes
1answer
83 views

Storing prices with price history and different specificity (global, user group, single user) in MySQL database

I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
1
vote
2answers
113 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
1
vote
2answers
76 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
1
vote
1answer
70 views

Is it more efficient to union from sub-query or combine two outer queries?

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked. I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
1
vote
1answer
148 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
1
vote
0answers
189 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
1
vote
2answers
265 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
-1
votes
2answers
82 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
0
votes
1answer
94 views

Subquery returned more than 1 value. please help. i beg for mercy

This is may table. In one column there are daily dates and in the other is a classification. I use this query to count how many registers are in a month and which ones are C or D, but the subquery ...
3
votes
3answers
2k views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId ...
2
votes
2answers
55 views

Need some assistance on MySQL Sub query

I have a table in MySQL. The table structure is as follows. id | emp_id | call_start | call_end | call_type -------------------------------------------------------- 1 | 20043 | ...
3
votes
1answer
139 views

Subquery magic in PostgreSQL

I have a query: update product_product set (write_date, default_code) = (LOCALTIMESTAMP, 'update') where product_tmpl_id in ( select distinct product_tmpl_id from product_template ...
1
vote
1answer
63 views

Why conditioning before and after join, gets different results?

I want to left join two tables and then return some of the records. My first query was: SELECT * FROM card LEFT OUTER JOIN user_card ON card.id = user_card.card_id WHERE card.package_id =14 AND ( ...
1
vote
2answers
44 views

Join Two queries with different declarations

I need to join two different queries to get some columns from both. The first one has a CTE. Query1: WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, ...
0
votes
1answer
43 views

Lookup multiple ids in seperate table with 2 subquerys

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date. SELECT PRTIMESHEET.POSTED_TIME, PRTIMESHEET.PRRESOURCEID as "SUBMITTER", ...
4
votes
1answer
116 views

Should I use a subquery to help SQL Server find the correct plan

I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
1
vote
0answers
47 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
0answers
163 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
0
votes
1answer
103 views

How to organize long Query

I have a massive query string that I am trying to pick apart for a project I am working on. I am looking for some guidance on how to take it apart(format it into human readable). Also, what are the ...
0
votes
0answers
33 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...
0
votes
2answers
42 views

Which query performs better when searching a row where a column has maximum?

Consider a simple table for example purposes: person(name varchar, age int) I have a task to retrieve the oldest person. Which query is better in general for such problems? Query 1: criteria with ...
0
votes
2answers
410 views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
1answer
88 views

How to subtract records from a table based on their existence in another

I have an Access 2010 table Assignment key Empl Number key Year Number key Week Number key Proj Number It assigns employees to zero or more projects for given work weeks, thus, all ...
2
votes
0answers
75 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here ...
0
votes
1answer
40 views

Passing filter params to a sub query

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
3
votes
1answer
370 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
0
votes
1answer
213 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
0
votes
2answers
29 views

In MySQL, get number of pregnant women who received 3 ANC checkups

I am working on a project where it is required to find out the total number of pregnant women who have received 3 ANC(Ante-Natal Care) checkups done. A pregnant women is said to receive 3 ANC ...
0
votes
2answers
76 views

Insert query with a subquery

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly ...