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