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.

104 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
2answers
102 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 ( ...
4
votes
1answer
310 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
1answer
695 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
21 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 | ----------------------------------------------------------...
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
645 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
33 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
273 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
93 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
1answer
160 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 ...
2
votes
0answers
87 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
833 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
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
727 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
0answers
37 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 ...
1
vote
2answers
26 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 ...
1
vote
0answers
15 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 ...
1
vote
0answers
14 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 ...
1
vote
2answers
37 views

How to get sum of 3 sub count queries and group them using an attribute

I have 3 queries as follows: For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries. Q1 SELECT count(*) FROM ...
1
vote
0answers
137 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" ...
1
vote
1answer
519 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
0answers
36 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
197 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
472 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
70 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
57 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
456 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
82 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
485 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
30 views

Getting Available Date/Times For Attendees And Avoid Conflict

+------------+ +---------------+ +--------------+ | attendees | | attendee_times| | meeting_times| +------------+ +---------------+ +--------------+ | attendee_id| | at_id | | ...
1
vote
2answers
3k 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
104 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
199 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
0answers
619 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
2k 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
20 views

Independent aggregation of related entities in a single statement, while sorting by related

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 ...
0
votes
1answer
44 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
0
votes
0answers
11 views

pulling most recent results for each point based on date field

so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to ...
0
votes
0answers
25 views

insert or update with summary subquery

i have a problem with follwing query INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
0
votes
1answer
17 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 ...
0
votes
1answer
13 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 ...
0
votes
2answers
33 views

Selecting distinct rows from two tables, using multiple conditions

I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
0
votes
1answer
39 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 ( ...
0
votes
0answers
12 views

Debugging performance differences (and issues) between a query with a double-subquery, a single-subquery and an all inner-join statements

I have a complex business logic that requires me to perform a 2-levels nested query. The queries are generated by Django's ORM. At the bottom of the question I'll provide the queries as-is as well as ...
0
votes
1answer
28 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 ...
0
votes
0answers
7 views

Count returning customers with several conditions

I have PostgreSQL 9.6.10 and a table like this one: CREATE TABLE web.orders (id integer NOT NULL, user_id integer NOT NULL, status_id integer, created_at integer); INSERT INTO web.orders(id, ...
0
votes
1answer
62 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 ...
0
votes
0answers
16 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 ...
0
votes
0answers
26 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....