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.
698
questions
0
votes
0
answers
15
views
Calculate (n) workdays from a given date, using a calendar table
As shown on below image, I have two tables;
Customer : with about 4 million records; and 3 columns
[Customer] : Customer ID
[Date] : Creation date
[Num_Days] : Number of working days to calculate ...
- 1
-3
votes
0
answers
35
views
Indexes to add to my queries
As an assignment, I had to write 3 queries that answer 3 different business questions with an emphasis on optimization (Tables consist billions of rows). I'm not working with any actual DB so ignore ...
- 1
1
vote
1
answer
93
views
Query for repeating sequences in SQLite
I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.
consider this example (http://sqlfiddle....
- 167
1
vote
0
answers
18
views
postgresql sub query
I have three tables
kit - contains kit_id and kit_type (kit type is with android or IOS)
appointment - contains appointment_id, created_datetime, kid_id, created_by_id
user - contains user_id, user ...
- 11
-3
votes
1
answer
75
views
subquery vs join which is better [closed]
SELECT *
FROM
table1
WHERE
t2id in
( SELECT id FROM table2
WHERE t3id =
(SELECT id FROM table3 WHERE id = '1') );
SELECT * ...
- 97
1
vote
1
answer
75
views
Return Subquery with Multiple Columns as JSON Array of JSON Objects
In my setup, the relevant tables for this problem are:
Items
Users
Collections (of Items) — each user can have multiple
Collection Items — where I track what items are in each collection.
The items ...
- 121
0
votes
0
answers
17
views
Join tables a and b, return only columns from a
I am writing some SQL to extract data from an outsource client's database and stick it into a data warehouse.
Once per day, a bunch of select statements will run against various useful tables in their ...
0
votes
1
answer
63
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
1
vote
1
answer
103
views
How to update a foreign key with random values from the referenced table?
In order to generate test data for a postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'.
How to do it?
I ...
0
votes
0
answers
16
views
Why SQL subquery returns no error on trying to select not existing column? [duplicate]
I use PostgreSQL 15 and have two tables:
table 1 has only one column id1 (pkey) and has 6 rows (values 1-6)
table 2 has only one column id2 (pkey) and has 6 rows with some another values
Then I try ...
- 1
0
votes
0
answers
18
views
Query that accesses 2 feilds of document in collection
I have a users collection of documents containing fields city_name, and income. How can I write a query which gives me the documents data of the top 10 cities which have the highest number of users ...
- 1
0
votes
1
answer
58
views
Compose a complex query in Postgresql
I have a huge table like following:
CREATE TABLE public.huge_table (
sampl_day date NOT NULL,
tick_time timestamp(6) with time zone NOT NULL,
crit_feat integer NOT NULL,
---
--- ...
- 285
1
vote
1
answer
82
views
How to return list of content from subquery for the main query?
I have a create that have sub query. To show as example, here is a query that has similarity with my own. Personally I have more table and more arguments on table (The subquery have left join for ...
- 205
2
votes
1
answer
3k
views
postgres - copy values from one column to another column (same table)
I want to copy the entire content from one column to another (same datatype) and thought of a subselect to be part of the equasion.
UPDATE tab01
SET column2=(SELECT column1 FROM tab01);
ERROR: ...
- 175
1
vote
0
answers
17
views
query customer retention over range
I am trying to find the best way to accomplish the following.
Get the beginning customer count, which carries from the previous day
Get New Customer count
Get the number of Customers who have not ...
- 111
1
vote
2
answers
85
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
0
votes
0
answers
59
views
Ranking players in sql database will return always 1 when where id_user is used
I basically have a table "race" with columns for "id_race", "id_user" and columns for user predictions "pole_position", "1st", "2nd", "...
1
vote
1
answer
85
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
- 43
0
votes
2
answers
87
views
Using a WHERE colname NOT LIKE (select * from otherTable)
So in essence what im trying to do is this:
Given TBL1 (around 8,000 rows)
letters
abababa
bcbcbcb
cdcdcdc
and TBL2 (around 2,000 rows)
letters
ab
bc
I want to write a query to find every row ...
0
votes
1
answer
134
views
Join result of crosstab to main query
I'm trying to get this column structure:
| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |
My table structure looks like this
CREATE TABLE products (
id SERIAL PRIMARY KEY,
...
2
votes
3
answers
4k
views
"ERROR: column "a" does not exist" when referencing column alias
I am attempting to retrieve either a null or boolean value from a query. Here is my query:
SELECT EXISTS (SELECT 1 FROM employee
where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') ...
- 123
1
vote
1
answer
216
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
0
votes
2
answers
33
views
I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments
I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both
This ...
0
votes
0
answers
25
views
Effect of correlated subquery used in an IN clause on the outer query?
I'm trying to understand in what way a correlated subquery affects the outer query when used in an IN clause.
This requires some prep code, so here goes:
Prep code
We have category and product ...
- 437
2
votes
2
answers
68
views
DB2 query not working
Customer (parent table)
Id
Charge Amt
C1
10
C2
20
C3
30
C4
40
Payment (child table)
Id
pymt amt
C1
10
C2
5
C4
40
Select Id and pending balance from customer and payment tables
Pending ...
- 21
1
vote
1
answer
112
views
DIV and GROUP by issues
I have one query, made it in MySQL.
And now try to run on PostgreSQL, but get syntax error twice.
Once on the DIV command, and then on the GROUP BY.
Here is my code:
UPDATE status_table
SET Status_CG ...
2
votes
1
answer
68
views
How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries
Problem
I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago)
Sample data
select version();
version()
...
- 79
0
votes
1
answer
334
views
How do I filter a set of child records to get a single child record for each parent? [duplicate]
Sorry if it's a simple question but I can't wrap my head around it. I have two tables, users and carts. For each user record I need to get it's most recent cart (ordered by created_at desc).
I've ...
- 103
0
votes
1
answer
75
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
2
answers
425
views
Postgres sort results by id's from subquery
In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows
...
- 15
0
votes
0
answers
32
views
How to subquery and FULL Join onto the same table
I have a SQL Table with alot of Team Names and Queries
For Example:
I want to do a FULL JOIN to compare the Red Team and Blue Team Only to see who is in Red, in Blue, or in Both. To Get Something ...
- 101
1
vote
1
answer
214
views
How to GROUP BY boolean when im already grouping by time frames 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time frame and grouped by bolean "R" or "L". I ...
- 29
0
votes
0
answers
23
views
Mysql Query need to optimize
I have below query having proper indexes (all columns in where clause), But still query is very slow.
Can anyone help me to optimize this mysql query?
SELECT
centerID,
compID,
...
- 1
0
votes
1
answer
166
views
How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users.
Example of tables ...
- 29
0
votes
1
answer
494
views
Why cannot I reference the second alias from a subquery containing a UNION?
Why cannot I use Qty2 as an alias in my query? I receive this error:
Error Code: 1054. Unknown column 'qty2' in 'field list'
SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty
...
4
votes
1
answer
251
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
0
votes
0
answers
49
views
How is semijoin used for decorrelating a nested query?
In Database System Concept book:
13.14 The set version of the semijoin operator semijoin is defined as follows:
r semijoin_theta s = Project_R (r innerjoin_theta s)
where R is the set of attributes ...
- 129
0
votes
0
answers
299
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 (...
- 1
-2
votes
1
answer
22
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 ...
- 1
0
votes
0
answers
85
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 ...
- 197
0
votes
2
answers
347
views
How to Select The Lowest Date in one field that's higher than the date in another field
The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:
I am trying to build a BigQuery Script which looks at all arcade ...
0
votes
1
answer
172
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 ...
2
votes
2
answers
80
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
...
- 123
0
votes
1
answer
277
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 ...
- 157
1
vote
3
answers
743
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 111
-1
votes
1
answer
986
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
523
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
161
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, @...
- 11
1
vote
1
answer
67
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',
...
- 27
0
votes
0
answers
107
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, ...
