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

2
votes
3answers
175 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 ...
3
votes
2answers
48 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 | ...
2
votes
1answer
83 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
53 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
38 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
29 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
95 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
29 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
39 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
74 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
28 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
40 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
47 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
20 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
31 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
35 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
158 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
47 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
18 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
58 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
44 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
369 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
48 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
128 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
47 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
64 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
74 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
27 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
47 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
73 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 ...
1
vote
1answer
57 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | ...
0
votes
1answer
31 views

Pick up the name of the column by two diff column in single table

Thank you guys in advance! i am desperately need to display my data in this way, My database structure is, Column idcat, parent_id and child_id is sharing single column name, catname. I tried ...
-2
votes
1answer
55 views

Single-row subquery returns more than one row

I try to run the follwoing query but it gives me 'Single-row subquery returns more than one row' error. BEGIN FOR C2 IN (SELECT S.bom_header, S.sap_cage_cd, ...
0
votes
2answers
75 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
3
votes
0answers
90 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
2
votes
1answer
166 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
0
votes
1answer
58 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
2
votes
1answer
52 views

Why is the subquery apparently correlated? How to avoid it?

I have a table orders and a table orderpositions. The orderpositions (basically the items bought with the order) are linked via orderid (indexed) to the orders primary key. For a given set of orders ...
0
votes
2answers
50 views

Delete the second record where a user has two records

I have a simple database scheme, which due to previously being badly set up has numerous duplicate records. record_table |--------------------------------------------------------| | id | subid | ...
0
votes
1answer
181 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
1answer
22 views

Querying All Records of All Users who have any instance of purchase behavior in their history

I am trying to write a query that shows me all user ids that in any instance of usage have a particular type of "purchase credit" associated with their account. I have one table, "users" that shows ...
1
vote
1answer
39 views

Determine existence in recursive working table

I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
0
votes
0answers
66 views

Multiple subqueries performance issue

I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer. The query looks like that: SELECT c.Id ...
0
votes
1answer
147 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
1
vote
1answer
72 views

Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]

I have the following query: SELECT COUNT(*) AS `count` FROM agreements WHERE start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y') AND start_date < ...
0
votes
1answer
88 views

problem with query two different tables with foreign key relations

I am having a problem with querying two tables with different foreign key relations... My first query works fine but when adding another table with a different set of relations I am having a ...
2
votes
1answer
3k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
2answers
83 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
1
vote
1answer
3k views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 ...
0
votes
0answers
52 views

Sub queries in Apache Hive

I have a table with following structure col1 col2 col3 col4 category 300 200 100 20 1 200 100 30 300 2 400 100 100 70 1 100 30 200 100 ...