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
0
votes
0
answers
363
views
MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly
I would like to write SQL query (for MySQL) that is able to SELECT
x rows by condition1 from table1
y rows by condition2 from table1+join1
z rows by condition3 from table1+join2
But on every page (...
-2
votes
1
answer
24
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
0
votes
0
answers
135
views
Is the use of a dependent subquery justified in this case?
SELECT a.id,
(SELECT ARRAY(
SELECT c.id FROM table_c c
JOIN table_d d ON d.id = c.id
JOIN table_e e ON e.id = d.id
WHERE a_id = a.id AND ...
0
votes
1
answer
480
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
0
votes
1
answer
300
views
Doesn't T-SQL support correlated CROSS JOINs?
This query works
SELECT QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM SWOPS.MIGR.QMCAddresses QMC
CROSS JOIN (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC WHERE FK=8055909) t1
CROSS JOIN ...
4
votes
2
answers
25k
views
Alternate to use LIMIT in subquery in mysql?
I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to ...
2
votes
2
answers
269
views
SELECT groups by individual members AND sum other values
Using MySqL 5.7.34 and having a table, groups_members, with 4 columns
ID
group
member
organization
1
A
Bob
Apple
2
A
Susan
Apple
3
A
Joe
Apple
4
B
Steve
Microsoft
5
B
Mike
Microsoft
6
B
Bob
...
-1
votes
1
answer
2k
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
0
votes
1
answer
628
views
How to map the newly created records in parent table with the child table?
have a table
Employees
Id Name EmployeeType_ID
1 John 1
2. Mathw 2
EmployeeTypes
Id TypeName
1 Perm
2. Temp
3. Regular
Now I am copying data from one server to ...
1
vote
0
answers
193
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
1
vote
1
answer
93
views
Mysql: sorting rows of a subquery generated table not working working
I have a subquery (generated by an ORM for pagination purposes) that generates a table that is then sorted by the parent query.
SELECT DISTINCT
`distinctAlias`.`invoice_id` AS 'ids_invoice_id',
...
1
vote
2
answers
17k
views
Count rows in a table based on a DATE field
I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:
order_id | client_id | date_added
-----------------------------------
14152 | NA4156 ...
1
vote
1
answer
1k
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
3
votes
2
answers
29k
views
Limit left join for multiple rows
Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.
Let's overview simple example.
...
0
votes
2
answers
10k
views
Selecting distinct rows from two tables, using multiple conditions
I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
0
votes
0
answers
202
views
MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery
I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
3
votes
2
answers
14k
views
Why does COALESCE function not work for this query?
I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can ...
2
votes
2
answers
6k
views
Debit Credit Balance calculating from previous records with Pagination
I have a table which contains the following data:
ID In Out
1 100.00 0.00
2 10.00 0.00
3 0.00 70.00
4 5.00 0.00
5 0.00 60.00
...
1
vote
0
answers
458
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
0
votes
1
answer
208
views
Finding and locally deleting deleted records in a million-record table
I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to ...
0
votes
1
answer
58
views
How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?
I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded.
Example Data:
CREATE TABLE ...
1
vote
3
answers
4k
views
Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query
A query like this:
SELECT *
FROM person
WHERE person.favorite_number = ANY(
ARRAY[ 2 , 3 ]
)
;
…runs. The ANY() function tells us if the single value is found within an array of values. So, ...
0
votes
1
answer
290
views
How to speed up subquery
Let's suppose we have:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
exists
(select
*
from
...
8
votes
2
answers
124
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
0
votes
0
answers
41
views
Finding duplicate records in a subset
I have 3 tables:
users
orders
order_items
I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
1
vote
1
answer
2k
views
How do I get the sum of a field and avoid duplication from an inner join?
I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%".
Accounts are unique, and there can be many ...
1
vote
2
answers
129
views
Help combining two MySQL queries to improve performance
I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query:
SELECT d.field_id_105 AS code,
title
FROM ...
6
votes
1
answer
780
views
Unused columns in WHERE clause
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
2
votes
1
answer
1k
views
Join query returning 0 rows - impossible where
My query is performed from a php web request using a PDO prepared statement.
My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order ...
1
vote
1
answer
1k
views
SQL Main query value used in the WHERE in the subquery
I'm really confused by this SQL here, I have this sample where I am using the value from the main query in the subquery and then using XML PATH to add a comma between the dates. The setup is 2 tables ...
0
votes
1
answer
41
views
What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?
ERD for my simple db design:
I want to order children based on their parent's last name. I am trying to use the following query:
SELECT *
FROM children
WHERE parent_id IN (SELECT parent_id
...
-1
votes
1
answer
4k
views
SQL query to join on maximum value on a per-row basis
I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers ...
0
votes
1
answer
687
views
how to use distinct on to avoid aggregating column in this query?
what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory
...
-2
votes
1
answer
488
views
SQL Subquery not working in Redshift
I'm trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars
Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) ...
0
votes
1
answer
77
views
Sum of Total per Client
I want to know the total of each product that each customer has bought and I don't know how to add the total per customer.
The tables are as follows:
CREATE TABLE CLIENTES -- Customers
(
CODIGO ...
0
votes
1
answer
159
views
How can I improve the run time of a query that groups / display same date data in one row?
I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
0
votes
1
answer
2k
views
MySQL date range if data not available display 0
I've a table here here is the structure : id,id_berita,ip_user,tanggal
I try to use this query :
select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00'...
2
votes
2
answers
2k
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
0
votes
1
answer
770
views
Understanding why getting each parent row joined with its latest child is not working when using subquery filter
I have a parent-children tables relationship, and I was trying to get each parent row with its latest child row details.
My attempts to do that using a sub-query didn't work, and I could only made ...
1
vote
1
answer
220
views
Postgres v10 is not using the index in a subquery
I have a subquery that will return 3 records, to later add the result of some calculations (using some functions).
When I am running the full query, Postgres is calculating a high number of expected ...
3
votes
2
answers
2k
views
Find the second highest value from a subquery or derived table
I have a subquery which returns the following results.
'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'
I want to find the second highest value from this....
0
votes
1
answer
48
views
Return all archive records when any one record meets criteria
I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September).
The Archive Table
SELECT PTA.PlnndTrnsfrID, PTA....
0
votes
1
answer
31
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID ...
0
votes
1
answer
799
views
SQL Insert by subquery inside update query [closed]
I want to Write a single query that updates columns in one table and insert rows in another table.
something like this
update
applications
set
applications....
0
votes
2
answers
857
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
0
votes
0
answers
61
views
How to avoid subquery duplication? Getting multiple rows of subquery
I need to create a view like this:
CREATE OR REPLACE VIEW bayer_pss_dashboard AS
SELECT
m.machine_id AS machine,
t.name AS time_interval,
(
SELECT SUM(TIMESTAMPDIFF(MINUTE, me....
-2
votes
1
answer
600
views
Having trouble with where subquery count
Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question:
SQL query to get the ...
3
votes
2
answers
13k
views
Update with subquery referencing the main table
I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
1
vote
1
answer
1k
views
Why is this postgres query ignoring the index in the subquery?
I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index:
sophia=> explain analyze select count(distinct(source_doc)) from public....
5
votes
1
answer
5k
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
...