Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...