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.
121 questions with no upvoted or accepted answers
0
votes
1
answer
65
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
0
votes
2
answers
57
views
I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments
I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both
This ...
0
votes
1
answer
120
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
0
answers
24
views
Mysql Query need to optimize
I have below query having proper indexes (all columns in where clause), But still query is very slow.
Can anyone help me to optimize this mysql query?
SELECT
centerID,
compID,
...
- 1
0
votes
0
answers
363
views
MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly
I would like to write SQL query (for MySQL) that is able to SELECT
x rows by condition1 from table1
y rows by condition2 from table1+join1
z rows by condition3 from table1+join2
But on every page (...
- 1
0
votes
0
answers
135
views
Is the use of a dependent subquery justified in this case?
SELECT a.id,
(SELECT ARRAY(
SELECT c.id FROM table_c c
JOIN table_d d ON d.id = c.id
JOIN table_e e ON e.id = d.id
WHERE a_id = a.id AND ...
- 209
0
votes
0
answers
202
views
MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery
I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
0
votes
1
answer
479
views
MYSQL SELECT if else in a subquery with default value in case that a value doesn't exit
I have the next scenario, DB: px
tables:
categories
category_for_level
clients_ip_to_user
full
levels
types
user
I need to check if there is a "url" in the full table which is in one of the ...
- 1
0
votes
1
answer
208
views
Finding and locally deleting deleted records in a million-record table
I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to ...
0
votes
1
answer
290
views
How to speed up subquery
Let's suppose we have:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
exists
(select
*
from
...
- 235
0
votes
0
answers
41
views
Finding duplicate records in a subset
I have 3 tables:
users
orders
order_items
I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
- 399
0
votes
1
answer
426
views
Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)
see the below query table.
Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby).
hobby: id, name
employee: id, first_name, last_name, age, mobile_number, ...
0
votes
1
answer
77
views
Sum of Total per Client
I want to know the total of each product that each customer has bought and I don't know how to add the total per customer.
The tables are as follows:
CREATE TABLE CLIENTES -- Customers
(
CODIGO ...
0
votes
0
answers
61
views
How to avoid subquery duplication? Getting multiple rows of subquery
I need to create a view like this:
CREATE OR REPLACE VIEW bayer_pss_dashboard AS
SELECT
m.machine_id AS machine,
t.name AS time_interval,
(
SELECT SUM(TIMESTAMPDIFF(MINUTE, me....
- 101
0
votes
0
answers
135
views
Postgres locking issue
I have the following Postgres table:
CREATE TABLE my_db.url_data (
url varchar(2500) NOT NULL,
status int,
priority int,
...
last_update_ts timestamp,
CONSTRAINT url_data_pk ...
0
votes
2
answers
1k
views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
0
votes
1
answer
3k
views
SQL sql subquery with SUM for totals
I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items ...
- 1
0
votes
1
answer
25
views
Having problems joining table
I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player.
The relational schemas are:
Emp_Players (eid,pid,team) composite key is (...
- 45
0
votes
0
answers
130
views
MySQL - Counting multiple parameters from the same subquery
I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
- 3
0
votes
1
answer
1k
views
SQL - Select everything after a certain row
After the following query:
SELECT
my_table.ID,
my_table.DATETIME,
my_table.VALUE,
diagnostics_keys.type as type,
diagnostics_keys.key,
diagnostics_keys.unit,
...
0
votes
2
answers
53
views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
- 101
0
votes
2
answers
73
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
0
votes
2
answers
473
views
MySQL subquery with WHERE filters not working as expected
I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
- 101
0
votes
1
answer
27
views
Return average using same table data
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from ...
- 141
0
votes
1
answer
101
views
Optimize query with a subquery (index is not used)
I have following query:
SELECT
`status`, `is_client_coordinator`, id, first_name, last_name
FROM `users` `t`
WHERE t.status!="CLOSED"
and t.is_client_coordinator
or t.id in (select ...
- 103
0
votes
1
answer
2k
views
PostgreSQL error: "subquery uses ungrouped column"
I have a query with subqueries that use only elements from the GROUP BY clause:
SELECT
DATE(cm.created_at) as "date",
COUNT(1) as total_message,
room_id,
(SELECT ...
- 111
0
votes
0
answers
38
views
Comparing sum against the average of all sums
I am looking for a way to compare the payroll of a team compared to the average of other teams in the same league. Here is the table structure, three standard tables and two connecting tables to ...
- 1
0
votes
0
answers
69
views
I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each
I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example):
SELECT wp_download_log.ID
,wp_download_log....
- 23
0
votes
0
answers
1k
views
Multiple sub query in insert statement for postgreSQL
I am trying to insert into table with multiple queries. And its not working, here is my work.
insert into post_topics_ta (id,post_id,topic_id,confidence_score)
SELECT * FROM post_topics WHERE ...
- 23
0
votes
0
answers
235
views
Improve MySQL query efficiency for first row in a group
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.
Reading other similar posts about how to access the first row in a group, it seemed like the ...
- 101
0
votes
1
answer
60
views
Count total unique rows for each date and group by date
I have the following table:
| email | date_added |
-------------------------
| [email protected] | 2019-01-01 |
| [email protected] | 2019-01-01 |
| [email protected] | 2019-01-02 |
| [email protected] | 2019-01-02 |
| [email protected] | 2019-01-...
- 199
0
votes
0
answers
32
views
MySQL query to reference previous field in subqueries
I have a question on how to best do this.
I have a table that contains how users are enrolled in a class, say "manual", "db", "email", etc. (enrolment type).
each class has a list of their own class ...
- 1
0
votes
1
answer
3k
views
Calculating percentage over sub query SQL
I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign.
First I calculated the ...
0
votes
1
answer
44
views
Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute
I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:
SELECT
(SELECT lead_id FROM leads WHERE to_id = lead_id OR ...
- 147
0
votes
2
answers
1k
views
Select/print data from subquery
In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user ...
- 483
0
votes
0
answers
45
views
Postresql using subquery select in order by clause
Is it possible to order by a selected columnname?
SELECT * FROM tablex ORDER BY quote_ident((
select column_name from information_schema.columns
where table_name = tablex AND column_name LIKE '...
- 53
0
votes
0
answers
1k
views
Subquery caching in join
I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell ...
- 65
0
votes
0
answers
35
views
Finding matching sub values?
So not too sure how to go about this, but ideally I want to pull out matching & non-matching info from an array of rows in a table, so I have a table with info like this:
12,2019-07-15 06:02:52,...
- 1
0
votes
1
answer
2k
views
Sql subquery for nested parent child data
I have two tables , on parent table (categories ) and child table (features).. I have this query
select c.* ,
(select GROUP_CONCAT(f.featuresId , f.featuresTitle )
from ...
0
votes
1
answer
39
views
MySQL - Optimize subquery in Select, where and order by
Im running a query like below.
SELECT DISTINCT c.`id`
, c.`job-cat`
, (
SELECT COUNT(*)
FROM `jobs` j
WHERE j.`mul_category` IN (c.`id`)
AND j.`ending` >...
- 1,960
0
votes
1
answer
51
views
How do I create a table with count of instances of an id before an event date that is individual to that id?
I am very new to SQL and have a relatively complex query to write, any help would be appreciated.
I have data with the following columns:
ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0)
I need a table ...
- 1
0
votes
0
answers
1k
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(...
- 101
0
votes
0
answers
34
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 ...
- 101
0
votes
1
answer
77
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
2
answers
65
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
1
answer
35
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" ...
- 101
0
votes
0
answers
45
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) ...
- 169
0
votes
1
answer
756
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
1
answer
45
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
1
answer
20
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 ...
- 101