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
8
votes
2
answers
124
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
- 1,569
5
votes
1
answer
556
views
Spatialite: use of virtual spatial index for distances
I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here
http://www.gaia-gis.it/gaia-sins/spatialite-...
- 51
4
votes
0
answers
3k
views
Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4
I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways:
-> XN Subquery Scan "*SELECT* 1"
-> XN Subquery Scan volt_dt_0
-> XN Seq Scan on ...
- 641
4
votes
1
answer
775
views
Unable to send WHERE parameters to subquery
I have the following problem (in PostgreSQL in combination with PostGIS):
I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
- 49
3
votes
0
answers
2k
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-...
- 256
3
votes
1
answer
5k
views
User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps
I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
- 189
2
votes
0
answers
33
views
subquery for history average is slower then expected
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
- 121
2
votes
0
answers
628
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
2
votes
1
answer
261
views
How to fetch related rows on multiple condition with ranking
I have following table for storing item information in PostgreSQL.
id | name | category | seller | m_age | status | type_id | region |
----------------------------------------------------------...
- 21
2
votes
0
answers
391
views
adding results of InfluxDB subqueries
I'm trying to get the kWh from multiple entities in InfluxDB in Grafana. I have the following raw query.
SELECT ("a" + "b") / 3600000 FROM (
SELECT spread("watt_seconds") AS "a" FROM "W"
...
- 21
2
votes
1
answer
2k
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'...
- 121
2
votes
0
answers
94
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 ...
- 21
2
votes
1
answer
40
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 | ...
- 23
2
votes
0
answers
303
views
Oracle 12c: sql query return less result when it's used in a subquery
I recently encountered a weird situation where i get different result from the same query by only changing its scope.
What i mean by changing the scope , is putting the query inside a subquery:
I ...
- 21
2
votes
0
answers
102
views
Classification of Subqueries?
Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories:
Scalar: returns a single value
Table-valued: Returns a table, to compare or add ...
- 875
2
votes
1
answer
275
views
Single MySQL query with row averages based on conditions
I have problem, where i know how to proceed with multiple queries, but dont really now
I Have a table with
BP {
name: varchar
systolic : int
diastolic : int
timestamp: date
}
I need to ...
- 21
2
votes
1
answer
742
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
- 141
2
votes
0
answers
924
views
Problem forming a query with subqueries in design view? (MS Access 2010)?
I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql.
That being said, I cant seem to ...
- 21
2
votes
0
answers
3k
views
Postgres Case statement not return else value in this query
I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck...
This is my query at ...
- 121
2
votes
1
answer
958
views
Very slow update with subquery (different tables)
I run this query to update "domains" table based on "log" table:
UPDATE domains
SET domains.mx_collected = 0
WHERE domains.domain IN
(SELECT log.mail as domain_name FROM log WHERE
`time` >= ...
- 21
1
vote
1
answer
20
views
In MS Studio how to SELECT from an existing SQL file
I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this ...
- 11
1
vote
0
answers
68
views
Inconsistent result when running same query on slightly distinct MariaDB versions
I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a ...
- 111
1
vote
0
answers
19
views
postgresql sub query
I have three tables
kit - contains kit_id and kit_type (kit type is with android or IOS)
appointment - contains appointment_id, created_datetime, kid_id, created_by_id
user - contains user_id, user ...
- 11
1
vote
0
answers
25
views
query customer retention over range
I am trying to find the best way to accomplish the following.
Get the beginning customer count, which carries from the previous day
Get New Customer count
Get the number of Customers who have not ...
- 111
1
vote
1
answer
499
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
1
vote
0
answers
193
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
- 11
1
vote
0
answers
458
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
1
vote
1
answer
502
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
1
vote
1
answer
93
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
1
vote
2
answers
537
views
MySQL - GROUP BY not working with multiple CASE WHEN statements
I am working on a database with Kickstarter database, and I'm trying to:
Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into ...
- 11
1
vote
0
answers
37
views
subquery returns more tha one row
I want to update data of a table where data-name matches with another table and also one data are common with many items.
For example, I have a cycle in which tyre is a part and quantity of tyre is ...
- 11
1
vote
0
answers
20
views
Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?
(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).
In ISO-compliant SQL ...
- 603
1
vote
1
answer
3k
views
Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)
I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.
with checked_adgroup AS (
...
1
vote
0
answers
43
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 ...
- 321
1
vote
0
answers
1k
views
Update Quantity Available using Quantity from another table
You will demonstrate an update that includes two tables and uses a subquery.
Make sure to prove that your update executed correctly by showing data in the tables before and after the update.
...
1
vote
0
answers
94
views
How to get total cost of start package table between two periods by sql query?
Problem: how to get total cost of start package table between two periods with a SQL query?
alexaPackage from 28/06/2017 to 05/07/2017 8days cost ?
Details
I need to get total cost in start ...
- 11
1
vote
0
answers
95
views
How to populate a 'map' table from words of a text?
Having the database structure that follows:
CREATE TABLE articles
(
article_id int(11) unsigned NOT NULL AUTO_INCREMENT,
article text,
PRIMARY KEY(article_id)
) ENGINE=InnoDB;
CREATE TABLE words
(
...
- 4,531
1
vote
0
answers
1k
views
Pass argument to deeply nested query
This overly complex query seems to work when I use a hardcoded value for gid=node.nid (eg. gid=123), but when I use it like below, I get the error following:
Unknown column 'node.nid' in 'where ...
- 111
1
vote
0
answers
35
views
Getting Available Date/Times For Attendees And Avoid Conflict
+------------+ +---------------+ +--------------+
| attendees | | attendee_times| | meeting_times|
+------------+ +---------------+ +--------------+
| attendee_id| | at_id | | ...
- 646
1
vote
2
answers
5k
views
MySQL Correlated Subquery (with multiple columns) per row
MySQL documentation states that:
Subqueries in the FROM clause cannot be correlated subqueries. They
are materialized in whole (evaluated to produce a result set) during
query execution, so ...
- 235
1
vote
2
answers
129
views
Help combining two MySQL queries to improve performance
I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query:
SELECT d.field_id_105 AS code,
title
FROM ...
- 11
1
vote
0
answers
227
views
Moving slow sub-query to With clause
I am trying to optimize this query :
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE
(
a-alias.created_at_minute_id >=
(floor(extract(epoch from(
select ...
- 111
1
vote
0
answers
626
views
Update row value with a sub-select join
I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table.
The query looks like this (...
- 123
0
votes
0
answers
20
views
Variable only resets per row when query has a static IN condition
I have a complicated query. I have a bunch of users. I have a ledger of debits and credits. I want to filter a user's subscribers by how long the subscribers have been negative. To do that I find ...
- 101
0
votes
0
answers
23
views
Lost connection to MySQL server during query for a not-so-complex long query
I have this query :
SELECT some_15_fields,
(SELECT GROUP_CONCAT(tb_inv_private_ip SEPARATOR ', ') FROM `network_interface`
WHERE network_interface.tb_inv_id=id) as 'private_ip',
(SELECT GROUP_CONCAT(...
- 279
0
votes
1
answer
33
views
How to Do a Sub Query and JOIN for Comparison Report
Hi I was trying to do a Sub Query and Comparison Report
For Example, I have a Table of Teams with Color AND Name in a Single Table.
A person can be on more than one Team and Multiple Team Colors can ...
- 101
0
votes
0
answers
38
views
Performance difference in SQL queries using JOINs with function
I have two SQL queries that are structurally the same, but have a significant difference in execution time.
The first query takes around 500ms to execute, while the second one takes only 50ms.
Both ...
- 33
0
votes
1
answer
54
views
Combine tables to find date of last value changed
Trying to query the date that a field changed to a zero amount. Here is some of the data from the tables involved:
-- mbMaster is the master table for all 'm' type tables (mvTable, mcTable), but does ...
- 75
0
votes
0
answers
32
views
How to use a parameter derived from a Table, given I'm filtering the table in query
The task is to find what fraction of total users have logged in at least for one more consecutive day after their first login.
SELECT
ROUND( COUNT( b.player_id )/COUNT( DISTINCT (SELECT a....
0
votes
1
answer
163
views
Calculate (n) workdays from a given date, using a calendar table
As shown on below image, I have two tables;
Customer : with about 4 million records; and 3 columns
[Customer] : Customer ID
[Date] : Creation date
[Num_Days] : Number of working days to calculate ...
- 1