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

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
90 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
89 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
94 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
46 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
394 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
122 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
138 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
267 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
80 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
109 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
74 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
138 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
172 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
226 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
80 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
85 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
1k 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
136 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
42 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
114 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
45 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 ...
0
votes
0answers
155 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
32 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
356 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
77 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
73 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
358 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
195 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
28 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
75 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 ...
0
votes
0answers
52 views

Smallest unique string among multiple columns

Somewhat similar to the Largest Common Substring (http://en.wikipedia.org/wiki/Longest_common_substring_problem) I'm trying to find the smallest unique string from a table. In some tables, its just ...
6
votes
3answers
3k views

Check if any of the values is in a subquery result

I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
0
votes
1answer
63 views

Using a subquery in a LIKE statement

I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how ...
4
votes
1answer
151 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
0
votes
0answers
86 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
1
vote
1answer
414 views

Call function where argument is a (sub)select statement

My function takes an int4 as argument and returns a table: SELECT * FROM test_function(545421); -- works fine SELECT * FROM test_function(SELECT customerid FROM tableX ...
1
vote
0answers
316 views

Amazon Redshift: “*SELECT* 1” vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
0
votes
0answers
31 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
1answer
95 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
1
vote
1answer
136 views

Alias of Subquery not working

I have been trying to get an order by to work correctly with an aggregate function. i.e. order by (bp + points) I also try adding these as a total amount from the following query however. If I try ...