Skip to main content

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
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 ...
adriano's user avatar
  • 33
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 ...
tvanc's user avatar
  • 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 ...
DTdev's user avatar
  • 101
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 ...
Geremia's user avatar
  • 103
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 ...
Borislav Zlatanov's user avatar
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(...
anjanesh's user avatar
  • 279
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 ...
horsepotatoe's user avatar
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 ...
user1207381's user avatar
0 votes
1 answer
128 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 ...
Rob's user avatar
  • 3
1 vote
1 answer
156 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 ...
Luke Hutchison's user avatar
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 ...
Timon's user avatar
  • 33
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....
Caspersky12's user avatar
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 ...
tkmagnet's user avatar
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 ...
Viper75's user avatar
  • 23
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 ...
tkmagnet's user avatar
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 ...
tkmagnet's user avatar
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 ...
TonyG614's user avatar
1 vote
1 answer
38 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 ...
Reno Kendo's user avatar
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....
Pixel_Bear's user avatar
-2 votes
3 answers
63 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 '...
Daniel Blake's user avatar
0 votes
1 answer
57 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 ...
jvkloc's user avatar
  • 133
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 ...
joanq's user avatar
  • 111
1 vote
2 answers
90 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 ...
MattB's user avatar
  • 111
1 vote
2 answers
93 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 ...
nyoatype's user avatar
0 votes
1 answer
103 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 ...
Marek's user avatar
  • 13
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 ...
LEOPOLDO's user avatar
1 vote
1 answer
320 views

Query for repeating sequences in SQLite

I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria. consider this example (http://sqlfiddle....
yossico's user avatar
  • 167
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 ...
Stephen 's user avatar
-3 votes
1 answer
131 views

subquery vs join which is better [closed]

SELECT * FROM table1 WHERE t2id in ( SELECT id FROM table2 WHERE t3id = (SELECT id FROM table3 WHERE id = '1') ); SELECT * ...
guradio's user avatar
  • 97
1 vote
1 answer
1k views

Return Subquery with Multiple Columns as JSON Array of JSON Objects

In my setup, the relevant tables for this problem are: Items Users Collections (of Items) — each user can have multiple Collection Items — where I track what items are in each collection. The items ...
psygo's user avatar
  • 121
0 votes
1 answer
65 views

Why the following first of two queries in MSSQL is faster as I want the second one?

1st Query: result 32 seconds (1,758,220 rows) SELECT CAST(ID.PYYY AS varchar(8))+','+ CAST(ID.SYYY AS varchar(8))+','+ ( SELECT TOP 1 ( CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+ CAST(...
mesutuk's user avatar
  • 101
2 votes
2 answers
558 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 ...
Edilson Vieira's user avatar
0 votes
0 answers
18 views

Why SQL subquery returns no error on trying to select not existing column? [duplicate]

I use PostgreSQL 15 and have two tables: table 1 has only one column id1 (pkey) and has 6 rows (values 1-6) table 2 has only one column id2 (pkey) and has 6 rows with some another values Then I try ...
Denis's user avatar
  • 1
0 votes
1 answer
74 views

Compose a complex query in Postgresql

I have a huge table like following: CREATE TABLE public.huge_table ( sampl_day date NOT NULL, tick_time timestamp(6) with time zone NOT NULL, crit_feat integer NOT NULL, --- --- ...
Leon's user avatar
  • 359
1 vote
1 answer
477 views

How to return list of content from subquery for the main query?

I have a create that have sub query. To show as example, here is a query that has similarity with my own. Personally I have more table and more arguments on table (The subquery have left join for ...
Elikill58's user avatar
  • 181
4 votes
1 answer
14k views

postgres - copy values from one column to another column (same table)

I want to copy the entire content from one column to another (same datatype) and thought of a subselect to be part of the equasion. UPDATE tab01 SET column2=(SELECT column1 FROM tab01); ERROR: ...
vrms's user avatar
  • 215
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 ...
thedemon's user avatar
  • 111
1 vote
2 answers
93 views

MySQL with subquery becomes slow

I have 3 MySQL tables: products, orders and users. I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
screaming SiLENCE's user avatar
1 vote
1 answer
162 views

Combining Multiple queries from the same table inrto 1 query

I am looking to find a way to achieve the below... I have tried using Joins and SubQueries but no joy as yet... I have a table that keeps track of wins/loses/draws as so Date WLD 01/01/23 Win 05/01/...
Paulmcf1987's user avatar
0 votes
2 answers
113 views

Using a WHERE colname NOT LIKE (select * from otherTable)

So in essence what im trying to do is this: Given TBL1 (around 8,000 rows) letters abababa bcbcbcb cdcdcdc and TBL2 (around 2,000 rows) letters ab bc I want to write a query to find every row ...
Shahzad Ansari's user avatar
0 votes
1 answer
436 views

Join result of crosstab to main query

I'm trying to get this column structure: | id | name | main_image | rating | shop_names | prices | urls | 2 | 3 | My table structure looks like this CREATE TABLE products ( id SERIAL PRIMARY KEY, ...
broken_cursor's user avatar
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') ...
Aamir's user avatar
  • 133
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. ...
Cowabunga's user avatar
  • 135
0 votes
2 answers
57 views

I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This ...
zaria palmer's user avatar
2 votes
2 answers
96 views

DB2 query not working

Customer (parent table) Id Charge Amt C1 10 C2 20 C3 30 C4 40 Payment (child table) Id pymt amt C1 10 C2 5 C4 40 Select Id and pending balance from customer and payment tables Pending ...
user2215220's user avatar
1 vote
1 answer
162 views

DIV and GROUP by issues

I have one query, made it in MySQL. And now try to run on PostgreSQL, but get syntax error twice. Once on the DIV command, and then on the GROUP BY. Here is my code: UPDATE status_table SET Status_CG ...
Database_operator's user avatar
2 votes
1 answer
74 views

How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries

Problem I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago) Sample data select version(); version() ...
bik555's user avatar
  • 79
0 votes
1 answer
515 views

How do I filter a set of child records to get a single child record for each parent? [duplicate]

Sorry if it's a simple question but I can't wrap my head around it. I have two tables, users and carts. For each user record I need to get it's most recent cart (ordered by created_at desc). I've ...
pinkfloyd90's user avatar
0 votes
1 answer
120 views

optimization with subquery not working as expected

I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows. Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
jwollbrett's user avatar
0 votes
2 answers
773 views

Postgres sort results by id's from subquery

In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows ...
LSM2236's user avatar
  • 15

1
2 3 4 5
15