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.
716 questions
3
votes
1
answer
2k
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 ...
- 267
0
votes
1
answer
843
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 ...
- 1
0
votes
2
answers
45
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 ...
- 103
0
votes
2
answers
6k
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 ...
- 1
8
votes
3
answers
44k
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 ...
- 83
1
vote
1
answer
7k
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 I'...
- 11
4
votes
1
answer
471
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 "...
- 343
13
votes
1
answer
29k
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 ...
- 245
4
votes
0
answers
3k
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 ...
- 641
0
votes
0
answers
62
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 ...
- 101
-1
votes
1
answer
191
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) ...
- 101
1
vote
1
answer
5k
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 ...
- 27
3
votes
1
answer
3k
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 |
|...
- 45
0
votes
1
answer
49
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 to ...
- 103
-2
votes
1
answer
1k
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,
I....
- 1
1
vote
1
answer
6k
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 ...
- 13
10
votes
1
answer
413
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 = ...
4
votes
1
answer
6k
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 ...
- 143
1
vote
1
answer
3k
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 "...
- 115
4
votes
1
answer
781
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 ...
- 142
0
votes
2
answers
94
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 | ...
- 101
0
votes
1
answer
37
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
1
answer
1k
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 ...
- 555
2
votes
1
answer
3k
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 ...
- 298
1
vote
1
answer
801
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 < STR_TO_DATE('15-01-2015','%d-...
- 21
0
votes
1
answer
221
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 problem......
- 3
33
votes
1
answer
178k
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 ...
- 433
2
votes
3
answers
2k
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) Check-...
- 21
3
votes
1
answer
70k
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 [email protected]
4 [email protected]
5 agent2@...
- 277
9
votes
2
answers
5k
views
Why wouldnt Mysql use index on the Primary query in a nested query
I have two tables: search_criteria and pricing.
There is an index on search_id column in search_criteria table and on pricing_id column in pricing table.
But running this nested query does not use ...
- 91
0
votes
1
answer
113
views
Email open rate calculations in subqueries with postgres
So I'm relatively new to PostgreSQL, but here we go:
I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays ...
- 1
1
vote
1
answer
75
views
Slow execution of a subquery with 0 matches
In MySQL, I have two basic tables - Posts and Followers:
CREATE TABLE Posts (
id int(11) NOT NULL AUTO_INCREMENT,
posted int(11) NOT NULL,
body varchar(512) NOT NULL,
authorId int(11) NOT ...
- 111
0
votes
2
answers
2k
views
Solution on a complex sub select query SQL Server
I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this:
Select
*,
(Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA,
...
- 101
3
votes
1
answer
1k
views
MySQL: Why queries with subqueries are much faster than single query?
I made few queries on my db lately
First query:
SELECT `table`.*
FROM (`table`)
WHERE table.id in(27172172,[...bunch of ids...],27171770)
ORDER BY field (`table`.`id`, 27172172,27172168,[......
- 41
1
vote
1
answer
166
views
Select products and include all cities it belong to
I have 2 tables :
PRODUCTS
+----------+----------+----------+----------+
| id | title | content | city |
+----------+----------+----------+----------+
CITIES
+----------+----------+
| ...
- 113
0
votes
1
answer
22k
views
Identify faulty sub-query (subquery returned more than 1 value)
I am trying to resolve an issue with a query that fails with this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when ...
- 83
0
votes
4
answers
115
views
Why does this query take 32 sec to execute?
I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
- 131
1
vote
1
answer
2k
views
Oracle: summing column value with subquery?
Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this ...
- 13
1
vote
2
answers
1k
views
Subquery or multiple left joins, or both?
I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
- 13
0
votes
1
answer
10k
views
Alternative to sub query in MS Access
I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are ...
0
votes
2
answers
665
views
Update specific records of table based on subquery's output (sql server)
have this query which produce below output :
select sec.site,sec.Name,sec.Freq from
( select Name from sec group by name having count(Freq) > 3)as dt
inner join sec on dt.Name = sec.Name
where sec....
- 669
0
votes
2
answers
362
views
How are derived table implemented in MySQL
When MySQL makes a derived table as a result of
SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)
Are these derived temporary tables created in-...
- 1,013
0
votes
3
answers
3k
views
How to rewrite subquery to return only 1 row
I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please?
SELECT DISTINCT user.id, user....
- 175
0
votes
1
answer
761
views
Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?
I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this:
select datetime, PLANT_STATUS, ...
- 75
0
votes
1
answer
155
views
SQLITE table joining with group
MESSAGE TABLE USERS
msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name
1 1 2 hi 22/1/14 ...
- 349
1
vote
2
answers
1k
views
#1093 - You can't specify target table 'R' for update in FROM clause
As an attempt to answer Difference data between adjacent rows
UPDATE Visits R
SET Duration = TIMEDIFF(
( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID ...
- 745
0
votes
2
answers
39
views
calculating max and joining tables
Just need a little help as new to SQL.
I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day.
...
5
votes
3
answers
5k
views
Compare multiple field to single IN query [closed]
I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg:
SELECT unit
FROM trips
WHERE owner1,owner2,owner3 in (select id from login)
Can this be done ...
- 1,956
2
votes
2
answers
3k
views
"All joins can be rewritten to sub queries", is this statement true?
I wonder if there any case that a join can not be converted to a sub-query.
BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
- 197
3
votes
1
answer
8k
views
CROSS APPLY versus INNER JOIN
I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well.
Maybe someone ...
