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.
716 questions
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 ...
CommunityBot
- 1
0
votes
2
answers
472
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 ...
CommunityBot
- 1
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'...
CommunityBot
- 1
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 ...
CommunityBot
- 1
2
votes
2
answers
556
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 ...
- 182k
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 ...
CommunityBot
- 1
2
votes
1
answer
741
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 ...
CommunityBot
- 1
3
votes
3
answers
11k
views
"ERROR: column "a" does not exist" when referencing column alias
I am attempting to retrieve either a null or boolean value from a query. Here is my query:
SELECT EXISTS (SELECT 1 FROM employee
where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') ...
- 182k
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,
...
CommunityBot
- 1
0
votes
1
answer
42
views
Add another subquery to one with a pivot
Obviously I need to study a lot more.
Erik Darling helped me with a great query to pull latest backup information (below) and Pivot on full and incremental. I wanted to add infor4mation if the backup ...
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
1
answer
37
views
using field of subquery in output
Query:
SELECT tbl1field
FROM tbl1
WHERE id = (SELECT t2id
FROM tbl2
WHERE anotherid = (SELECT t3id FROM tbl3 )
)
here tbl3 is having 2 fields : t3id and ...
- 9,480
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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-...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
424
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, ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
2
votes
4
answers
2k
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 101
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 (
...
CommunityBot
- 1
1
vote
1
answer
498
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.
...
CommunityBot
- 1
4
votes
1
answer
774
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 ...
CommunityBot
- 1
0
votes
1
answer
27
views
Get difference in values of second table from IDs in two columns of first table?
Given these two tables:
Table1
id1
id2
224
2341
5234
9394
6243
2342
⋮
⋮
Table2
id
value
224
2
2341
52
2342
24
5234
23
6243
242
9394
99
⋮
⋮
I would like to create this table:
Table3
id1
...
- 23.6k
0
votes
3
answers
57
views
Dynamic column selection in a subquery
I have two tables, one containing polls and the other poll votes, like this (simplified):
CREATE TABLE IF NOT EXISTS polls (
id SERIAL PRIMARY KEY,
token VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS ...
2
votes
2
answers
68
views
Need some assistance on MySQL Sub query
I have a table in MySQL.
The table structure is as follows.
id | emp_id | call_start | call_end | call_type
--------------------------------------------------------
1 | 20043 | 20:20:...
- 316
1
vote
1
answer
845
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 ...
CommunityBot
- 1
1
vote
3
answers
416
views
Need help rewriting view to avoid using subqueries in MySQL 5.1 db
I am working on creating a view in MySQL for an application that tracks form submissions. There is one particular table that I cannot figure out how to write a query for. It has foreign_keys ...
- 19
0
votes
1
answer
127
views
Debug RPC Function Creation in Supabase
I'm pretty new to database stuff.
I'm using Supabase to create an application where I keep track of the number of likes on certain items ('clicks'). I want to filter items either by the date the likes ...
- 182k
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
11
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 ...
- 98.6k
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 ...
- 968
1
vote
1
answer
155
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 ...
- 182k
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
11
votes
2
answers
15k
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 ...
- 182k
0
votes
1
answer
42
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....
- 39.5k
7
votes
4
answers
85k
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,762
1
vote
2
answers
616
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 ...
CommunityBot
- 1
2
votes
3
answers
682
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 ...
- 651
0
votes
3
answers
3k
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 ...
- 39.5k
1
vote
2
answers
67
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 ...
- 75
2
votes
1
answer
64
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 ...
- 14.4k
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 ...
- 16.6k