Questions tagged [subquery]

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.

Filter by
Sorted by
Tagged with
5
votes
3answers
5k views

Query to find GUID's “not in” null containing subquery returns no results

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
7
votes
2answers
2k views

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685. I have a simple table layout: class person: belongs to a class I want to ...
2
votes
3answers
3k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, dbo.ORDER_D....
4
votes
3answers
313 views

Correlated Subquery in SQL Server 2014

I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state ...
3
votes
1answer
25k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
2
votes
1answer
2k views

Aliases with correlated subqueries

I am looking at an example of correlated subquery with aliases from a PostgreSQL book: bpsimple=# SELECT * FROM orderinfo o, bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c bpsimple=# ...
37
votes
3answers
5k views

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From ...
13
votes
1answer
60k 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 ...
5
votes
1answer
2k views

Why does adding ROWNUM to a query improve performance?

I have two queries: 1) This query has a ROWNUM column (takes 20 seconds to execute): SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y ...
4
votes
1answer
3k views

Using column alias in a WHERE clause doesn't work

Given a table users with two fields: id and email. select id, email as electronic_mail from ( select id, email from users ) t where electronic_mail = '' Postgres complains that: ...
4
votes
2answers
555 views

How can i return the max of a sum by each team?

I have a lot of teams and i want to know the maximum of sum total of each team. This's my query : SELECT campaign_id, campaign_identifier, team, campaign_name, Month(...
4
votes
2answers
3k views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
2
votes
1answer
6k views

How to select a subset from a query result

I have a query which calculates the Time Difference and Distance Between the Next and Previous Row: SELECT * FROM ( SELECT iVehicleMonitoringId AS PreviousId, ...
2
votes
1answer
370 views

Deduplicate Data Using Having with Subquery

I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward. I have this ...
1
vote
1answer
55 views

conversion of a non correlated sub query to a join statement

Is it possible to get rid of the sub query and convert the entire query into a JOIN query? SELECT student_id, COUNT(attendance) FROM student_attendance WHERE attendance = 'ABSENT' GROUP BY ...
0
votes
2answers
3k views

Performing query based on another query's result

First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single ...
6
votes
2answers
2k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
3
votes
3answers
3k views

Performance of count(*) in subquery

Suppose we have the following queries: 1. SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val' 2. SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
3
votes
2answers
251 views

Join is causing other column data to change?

I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created. I am trying to join the ...
3
votes
1answer
2k views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
2
votes
1answer
110 views

Why Subquery do not filter the result in multiple Subquery

This is Query (skipped selection for simplicity and also it work perfect): SELECT * FROM (SELECT a.striker FROM ball_by_ball a LEFT JOIN batsman_scored b USING (...
2
votes
2answers
1k views

Optimize a query with two range conditions

I have a structure similar to this one: CREATE TABLE `author` ( `id` int(11) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=utf8 ...
2
votes
1answer
942 views

Updating a table with duplicate id's while avoiding “ single-row subquery returns more than one row” [closed]

I'm currently making SQL that'll update data from 1 table, based on data from another table for my company update table1 set column1 = (SELECT color FROM table2 Where ...
2
votes
1answer
3k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
1
vote
2answers
5k 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
vote
3answers
248 views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
1
vote
1answer
748 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
1
vote
1answer
1k 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 "...