Stack Exchange Network

Stack Exchange network consists of 174 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.

0
votes
1answer
12 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 ...
0
votes
0answers
16 views

Upgrade query with LEAD simulation in mysql

I'm here for ask how to upgrade this query localized in SQL Fiddle. There, we have column Time_Stamp with dates of two days diferrents, and value column where values they vary between 0 and 1 ...
-3
votes
1answer
60 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
37 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
22 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
24 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
31 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
9 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
44 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
46 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 '...
2
votes
2answers
152 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
10 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
46 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
39 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
30 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
135 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
84 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
33 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
34 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
37 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
168 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
73 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, [...
5
votes
2answers
242 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
369 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
27 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
65 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 ...
3
votes
2answers
636 views

case statement sub query need to return multiple values

Below query need to return multiple values select CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!='' THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject WHERE ...
1
vote
1answer
54 views

Logic of Assigning values to variables in Scalar-valued Functions

I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs. While troubleshooting my rewrites I came across some ...
2
votes
1answer
27 views

Consults and counts in multiple databases

I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer. My problem - I have 3 tables: Clients ------------------------------------------------------ | id | ...
0
votes
0answers
88 views

Query to Calculate Consumption from Reading Table

I have a Reading Table with following details: Reading(rea_ide(PK), rea_ini, rea_dat, met_ide(FK)) I also have a Meter Table Explanation: The bill is received for a ‘Main Meter’ and then this bill ...
0
votes
0answers
40 views

Divide and Update the Hours in SQL Server

I have two tables: 1. ForemanReports having columns ID, Date etc. 2. FRToMainsInstallation having columns ID, ForemanReportID(Primary ID of ForemanReports Table), Hours. ForemanReports: ID &...
0
votes
2answers
36 views

Is it possible to have both a subquery and a function in a WITH clause?

In Oracle 12c, we can include subqueries in the WITH clause: WITH dept_count AS ( SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) SELECT e.ename AS employee_name, dc....
0
votes
1answer
23 views

MySQL select all records unless user must be present in favorites

I am having trouble to find the best solution for the following problem; Description: I want to select all records from an items table, unless it is for favorites only, in that case the user must be ...
1
vote
1answer
38 views

Update value with nested subqueries

I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C) I have tried something like this which does work if I have only the first select ...
0
votes
2answers
65 views

handle subquery with joins where same result is to be joined with multiple tables

I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in ...
0
votes
3answers
42 views

Optimize query to filter active IDs for a given day from big table

I have a PostgreSQL table called users with 4 columns: user_id integer, -- we can have a lot of the same user_id name text, -- name of the user value text NOT NULL, -- ...
0
votes
0answers
43 views

How can I decrease my running time? - using MS Access sub query with 480 rows

Im tasked with creating a budget tool in MS Access 2013 for which it shows a 'dynamic budget', meaning the leftover budget should be divided over the months left in the year. Should there be any ...
-1
votes
1answer
446 views

Replace Subquery with JOIN - MYSQL

Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query : SELECT sa.id AS patAppIdSA, sa.rte_id AS patRTEId, sa.case_type_id AS ...
1
vote
1answer
33 views

Very weird result of CTEs and subqueries [closed]

WITH t1 AS (SELECT a.id, AVG(standard_qty) std_avg, AVG(poster_qty) pos_avg, AVG(glossy_qty) gloss_avg FROM accounts a ...
2
votes
1answer
101 views

Deduplicate Data Using Having with Subquery

I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward. I have this ...
1
vote
1answer
268 views

Why does `row_to_json(…)` accept a sub-query?

I recently encountered this example (via a tutorial and other answer): SELECT row_to_json(x) FROM (SELECT id, title FROM my_table) x; I'm confused about the use of row_to_json(x). I thought it ...
0
votes
1answer
111 views

MS Access M:N Relationship query: exclude records with more than the requested values

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here: Let's ...
1
vote
1answer
54 views

conversion of a non correlated sub query to a join statement

Is it possible to get rid of the sub query and convert the entire query into a JOIN query? SELECT student_id, COUNT(attendance) FROM student_attendance WHERE attendance = 'ABSENT' GROUP BY ...
0
votes
1answer
384 views

Trying to run query and getting error: Error 1064 (42000)

I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine. I am getting this error: ERROR 1064 (42000): You have an error in your SQL ...
1
vote
2answers
60 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
1
vote
1answer
81 views

multiple insert queries after a set of with's

I am trying to have a reproducible way to populate a database with some test data. Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-...
-1
votes
1answer
23 views

Ambiguous syntax for IN clause

I am working on some select query and I found this syntax SELECT NOM FROM INGENIEUR A WHERE 7 IN (SELECT CODEPT FROM EMPLOI WHERE MLE = A.MLE) I have never seen this ...
1
vote
1answer
19 views

Join or Replace colum value based on query

I have this query: SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description FROM items as i LEFT JOIN hosts as h ON i.hostid = h.hostid AND i.type = 11 AND i....
2
votes
2answers
3k views

user parent table id in nested subquery

SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM (SELECT user_id FROM ( SELECT sender_id AS user_id ...