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.
1
vote
1answer
19 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 (...
0
votes
0answers
25 views
MySQL How to get first row from subquery when using aggregate function
This is my Query:
SELECT
COUNT(runs_scored) - SUM(IF(extra_type_id = 4, 1, 0)) AS Balls,
SUM(runs_scored) + SUM(IF(extra_type_id IN (2 , 4),
extra_runs,
0)) AS Runs,
SUM(...
0
votes
0answers
12 views
How to find full siblings? I have been able to find the parents, but can't figure out how to join siblings based on having same parents
I have two tables (table/column names not my choice):
| Person | | Parent |
------------------------------ -----------------------
| ...
0
votes
0answers
19 views
Combine multiple queries that each require a row of input values
I'm looking for a way to combine multiple queries that each need two values.
For example, I can look up a patient's age and weight as follows:
select age
, weight
from patients
join ...
0
votes
1answer
27 views
How to search records based on date range for other matching data
I have below records in table
APP_No Stage Start_Date End_Date
A123 L1 01-01-19 02-01-19
A123 D1 01-03-19 05-03-19
A345 L1 01-01-19 02-01-19
A345 D1 01-03-19 05-03-19
I want ...
0
votes
2answers
57 views
How to join these queries?
So I currently have this query:
SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r....
0
votes
1answer
19 views
How to manually set a return value to a subquery with multiple results
I have a subquery which can return multiple results if a customer has duplicated sales codes.
I currently LIMIT 1 to just return the most recent but ... Is there away to return NULL or "DUPLICATE" ...
0
votes
1answer
35 views
Multicondition Subquery Max Date and Item Number
The following is a query to get latest Sales date for a customer.
select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
select max(SalesDate)
from ...
0
votes
2answers
38 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
1answer
38 views
Why Output is wrong after doing the left join as subquery
I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.?
Query 1:
select Batsman.innings_no,
bowler,
sum(...
1
vote
1answer
26 views
Using a Calculated Field in a Separate Calculated Field
I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field. For example:
,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
...
5
votes
1answer
50 views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
-1
votes
1answer
39 views
How to do group by based on another column in SELECT? [closed]
I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an ...
0
votes
0answers
30 views
Comparing count of 2 tables, is this the correct way?
I'm building a survey system where
A client that has many users
A user can have multiple surveys.
I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
1
vote
3answers
89 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 ...
2
votes
0answers
24 views
Is it possible to replace count distinct inside Correlated sub-queries?
I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...
Is it ...
1
vote
1answer
156 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 ...
1
vote
1answer
161 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 '...
0
votes
1answer
13 views
Getting aggregate sub-values in aggregate query
I have a query that need to show aggregated values in an already aggregated outer query.
Consider a query that fetches total count of accounts being credited on a certain day.
I would like to ...
1
vote
1answer
33 views
Update rows in table with id from row before in PostgreSQL
Say I have a table like this:
| id | grp | time_added | previous_id |
|----|-------|------------|-------------|
| 1 | 1 | 5 | null |
| 2 | 1 | 8 | null |
| ...
0
votes
1answer
67 views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
0answers
18 views
PostgreSQL - Optimize query with multiple subqueries
I have 2 tables, users and sessions. The tables look like this:
users - id (int), name (varchar)
sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar)
All columns have an index....
3
votes
1answer
100 views
Correlated Subquery Versus Non-Correlated Subquery
There are three tables that may be used by a business to keep track of the absent days of its employees. It's from a collection of SQL puzzles by Joe Celko. The reader is tasked with concocting a ...
1
vote
1answer
127 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:
...
0
votes
1answer
55 views
MySQL Subquery count
I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding ...
-4
votes
1answer
91 views
Selecting data from a sub query
I am doing the exercises this is the link (https://www.wiseowl.co.uk/sql/exercises/standard/subqueries/4228/).
Can anyone help me. Thanks in advance!
Question
Write a SELECT statement to return ...
1
vote
1answer
38 views
Update “oldest” row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
0
votes
1answer
25 views
Which query execution will be faster
In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.
Also, can you guys suggest some better tools ...
0
votes
1answer
87 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
1answer
49 views
How to join 3 tables in MySQL
I’m looking at 3 tables here with:
1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign ...
0
votes
1answer
11 views
why is this postgresql query returning only zeros?
select g.ad_id, count(1) as total,
(select count(1) from geoData g1 WHERE g1.ad_id=g.ad_id and
utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59') NOV1,
(select ...
1
vote
2answers
250 views
MySQL unknown column in subquery
I'm running a fantasy game where the ratings for each player are calculated in the following way:
Each round (gameweek) a player plays zero, one or more games. The rating for that round is the ...
-1
votes
2answers
100 views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
3
votes
2answers
326 views
Subquery gives no error for a non-existing column with the same name as in the outer query
I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.
I run this query:
select * from t1 where c1 in (select c1 from t2);
The above query should give an error as c1 ...
1
vote
1answer
12 views
Query for existence of objects across two multi-to-one relationships
I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".
The "Config" ...
0
votes
3answers
54 views
How to select specific items from table A which appear with all items of some column in table B?
I have 2 tables with the following schemas:
t1(id, type) where id and type are the primary key
t2(type, rate) where type is the primary key
I want to find all id's which appear with all types from ...
1
vote
1answer
40 views
Mysql subquery not recognizing the subquery join table field
I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the ...
1
vote
0answers
32 views
How to create subquery on multiple result columns?
I have two tables, both sharing the following columns:
table inventory: id, key, timestamp
table stocks: id, key, timestamp
I want to select all occurrences of the key from inventory table together ...
2
votes
0answers
255 views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
-1
votes
1answer
172 views
Randomly select 4 items of a randomly selected category
I need an idea how to construct this query.
I have a web page. each time the page is refreshed, I want to select data from a random category, and to select no more than 4 random items from that ...
0
votes
4answers
44 views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
-2
votes
1answer
38 views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
-1
votes
1answer
38 views
Please help me figure out this data. I am trying to calculate CTR(click through rate). Should I be grouping by the CampaignId?
my data looks like this:
I am trying to calculate CTR, but nor sure how to group it to show some kind of KPI (also open to suggestions on what should I present).
SELECT click_n, imp_n, CAST(((...
0
votes
2answers
1k 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 ...
0
votes
1answer
76 views
Finding the last record within three years of the first record with subqueries [closed]
I have the following table with student enrollment data:
CREATE TABLE [dbo].[Enrollments](
[SequenceID] [bigint] NULL,
[SchoolYear] [int] NULL,
[StudentID] [varchar](8) NULL,
[...
6
votes
2answers
596 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
1answer
583 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 ...
0
votes
1answer
32 views
How to select events based an adjacent times?
I have a table rooms which contains information regarding events that take in rooms. This is the definition of the table:
create table room(
sdate date,--start date
stime time,--start time
rno ...
0
votes
1answer
57 views
How to make subtraction without repeating the subqueries? [closed]
I need to efficiently obtain:
players who play more than one world cup and who have never been champion.
primary-key | foreign-key | pk-and-fk
PLAYER<player_id, player_name, country_player, .....
1
vote
2answers
72 views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
