Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
1 vote
1 answer
461 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 ...
9 votes
2 answers
4k 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 ...
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 ...
0 votes
1 answer
22 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 ...
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 ...
0 votes
0 answers
12 views

Debug RPC Function Creation in Supabase

I've never asked a question before, but I've read what feels like a zillion answers and still can't get my function to work. I'm pretty new to database stuff in general. I'm using Supabase to create ...
1 vote
1 answer
356 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. ...
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 ( ...
4 votes
1 answer
764 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 ...
1 vote
1 answer
45 views

Limiting the number of rows that can be inserted for a given WHERE clause

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions? I have a table event_attendance with ...
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'...
1 vote
2 answers
487 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 ...
0 votes
0 answers
25 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 ...
0 votes
1 answer
47 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 ...
0 votes
2 answers
366 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 ...
10 votes
2 answers
14k views

How to combine ORDER BY and LIMIT with an aggregate function?

Here is a fiddle for my question. I have a simple table layout: class person: belongs to a class I want to select all classes, and for each class, I want the first two person identifiers of the ...
0 votes
1 answer
40 views

Display in asc order from multiple databases under multiple linked server and add two date parameters so that I can integrate it to my app

I have a script below where it display a result from different databases under different linked Server. The query is just connected or linked thru union all and it seems there's no issue on its result....
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 ...
0 votes
1 answer
1k views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces ...
7 votes
4 answers
82k views

MYSQL Subquery in columns of select statement

How are subqueries in the column field of select (projection) paired with the result of the main query? in the form: SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name FROM ...
1 vote
1 answer
352 views

How to update a foreign key with random values from the referenced table?

In order to generate test data for a postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'. How to do it? I ...
1 vote
2 answers
521 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 ...
2 votes
3 answers
629 views

How can I get an average from a query dataset in SQL Server?

I am building a parts inventory system, and have a transaction table that stores how often an item in used. Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
2 votes
1 answer
565 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 ...
0 votes
1 answer
943 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
3 answers
2k views

I am trying to find the average of a COUNT request [closed]

I have completed the query below from an invoices table which gives me the total invoices attached to each house ID. So house_id 114 has 14 invoices attached to it. Is there a way to feed this result ...
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 ...
1 vote
2 answers
51 views

Query to return latest record between 2 tables

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data: create table customer ( cusID char(5), cusPurDt ...
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 ...
0 votes
1 answer
712 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 ...
2 votes
1 answer
53 views

Query for single return meeting multiple criteria and values

I'm trying to figure out a way to count an ID once when multiple criteria are met in one column while displaying a total of the same criteria in another column. The criteria for the first column can ...
0 votes
1 answer
420 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 vote
1 answer
19 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 ...
1 vote
1 answer
808 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 ...
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 ...
5 votes
1 answer
539 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
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 ...
0 votes
1 answer
2k views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
0 votes
1 answer
383 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
2 answers
999 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 ...
1 vote
1 answer
33 views

MySQL Tennis Ladder View Creation Question

I am creating a Tennis Ladder VIEW using MySQL and wish to do the following: Properly create a view that includes: All player's names These player names will come from the table: User - using the ...
0 votes
0 answers
30 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....
-2 votes
3 answers
52 views

Query to find values based on a like and a not like

I have a column in a table: ID - this column can have a value of '123456' but can also have value '123456x'. I need to return all the numbers where there is an 'x' and the same number but without an '...
0 votes
1 answer
47 views

Efficient PostgreSQL query syntax for duplicating the result

Assuming I have the following PostgreSQL query: SELECT ( SELECT SUM (t.customers) FILTER ( WHERE id = 1 AND date BETWEEN %(start)s AND %(stop)s ...
46 votes
4 answers
63k views

Using column alias in a WHERE clause doesn't work

Given a table users with two fields: id and email. select id, email as electronic_mail from ( select id, email from users ) t where electronic_mail = '' Postgres complains that: ...
1 vote
2 answers
61 views

Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
1 vote
0 answers
53 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 ...
26 votes
2 answers
139k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
0 votes
2 answers
71 views

How do I optimize this query that gets the newest entry of each group?

I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
0 votes
1 answer
90 views

The most recent rows in for each type/agent

I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...

1
2 3 4 5
15