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.
4
votes
1answer
79 views
How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?
It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.
In the following, I have a query ...
1
vote
1answer
34 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
1answer
27 views
Two aggregated sub queries wont run
I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? ...
-1
votes
0answers
43 views
SQL Query Help Required
It's been a while since I last wrote any SQL. Hoping someone can help me get started with the query here as my brain has apparently liquefied the SQL part in the intervening time.
I need to select a ...
2
votes
2answers
60 views
What am I doing wrong with subquery
I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?
Question: Show ...
3
votes
1answer
50 views
Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
1answer
64 views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
0
votes
1answer
64 views
Having a subquery somehow do the same group by as the outer query
i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.
Basically we'...
2
votes
3answers
62 views
Return ALL rows based on a matching date also matching a specific value in another column
I'm using SQL Server 2014, exact version: 12.0.5579.0.
I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just ...
0
votes
1answer
43 views
When to use lateral joins vs correlated subqueries in Postgres?
These two queries:
SELECT username, location, task, ts_next - ts AS diff
FROM (
SELECT username, location, task, ts, (
SELECT ts AS ts_next
FROM switches
...
0
votes
2answers
50 views
MySQL Function work very slow
I have writed this mysql function, it's worked perfekt but very slowly, what can I do for better performance, have you any idea?
note:i need all created virtual columns
DROP FUNCTION IF EXISTS ...
-3
votes
1answer
54 views
Print Two Different Columns with Union All- SQL
I have an SQL statement like this,
SELECT *
FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"),
(select "Doe" as LastName union all select "Queen" union all ...
2
votes
1answer
76 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
28 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
20 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
30 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
59 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
91 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
84 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
39 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
55 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
40 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
118 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
760 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
327 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
116 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
30 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
109 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 ...
3
votes
1answer
403 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
108 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
109 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
42 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
137 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, ...
-1
votes
1answer
206 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
328 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
171 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
480 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
60 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
33 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 ...
