We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

87 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
2answers
89 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 ( ...
5
votes
1answer
497 views

Storing prices with price history and different specificity (global, user group, single user) in MySQL database

I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
4
votes
1answer
260 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-...
3
votes
1answer
2k 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 ...
3
votes
2answers
152 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
2
votes
0answers
27 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 ...
2
votes
0answers
465 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-...
2
votes
1answer
32 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 | ...
2
votes
0answers
267 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 ...
2
votes
0answers
80 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 ...
2
votes
0answers
74 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 ...
2
votes
0answers
763 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 ...
2
votes
1answer
666 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 ...
2
votes
0answers
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 ...
2
votes
1answer
681 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` >= ...
1
vote
1answer
215 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'...
1
vote
1answer
4k 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
0answers
34 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 ...
1
vote
2answers
157 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
0answers
332 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
1answer
52 views

Select the Scheme and Table based on Select result in Postgresql

I have this query on postgree... SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn FROM pg_class a INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid) INNER JOIN ...
1
vote
0answers
53 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 ...
1
vote
1answer
385 views

Do too many subquries create multiple sleeping processes in MySQL?

I am using java & MySQL to make an app. When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed. I have read many ...
1
vote
0answers
79 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 ( ...
1
vote
0answers
362 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 ...
1
vote
0answers
28 views

Getting Available Date/Times For Attendees And Avoid Conflict

+------------+ +---------------+ +--------------+ | attendees | | attendee_times| | meeting_times| +------------+ +---------------+ +--------------+ | attendee_id| | at_id | | ...
1
vote
2answers
2k 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 ...
1
vote
2answers
92 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 ...
1
vote
0answers
177 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 ...
1
vote
1answer
129 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 ...
1
vote
0answers
614 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 (...
1
vote
0answers
1k 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 ...
0
votes
1answer
31 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
1answer
34 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 ...
0
votes
1answer
52 views

Aggregate function on subquery

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order: Offer -> Order -> Delivery -> Invoice. For each new row their ...
0
votes
0answers
14 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" ...
0
votes
2answers
40 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 ...
0
votes
0answers
20 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 '...
0
votes
0answers
84 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 ...
0
votes
0answers
24 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,...
0
votes
0answers
23 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
0
votes
0answers
217 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
1answer
21 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` >...
0
votes
1answer
33 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 ...
0
votes
0answers
42 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
15 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
20 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
37 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
59 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" ...