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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

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