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.

Filter by
Sorted by
Tagged with
0
votes
1answer
45 views

MySQL select all records unless user must be present in favorites

I am having trouble to find the best solution for the following problem; Description: I want to select all records from an items table, unless it is for favorites only, in that case the user must be ...
1
vote
1answer
152 views

Update value with nested subqueries

I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C) I have tried something like this which does work if I have only the first select ...
0
votes
2answers
160 views

handle subquery with joins where same result is to be joined with multiple tables

I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in ...
0
votes
3answers
42 views

Optimize query to filter active IDs for a given day from big table

I have a PostgreSQL table called users with 4 columns: user_id integer, -- we can have a lot of the same user_id name text, -- name of the user value text NOT NULL, -- ...
-1
votes
1answer
1k views

Replace Subquery with JOIN - MYSQL

Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query : SELECT sa.id AS patAppIdSA, sa.rte_id AS patRTEId, sa.case_type_id AS ...
1
vote
1answer
33 views

Very weird result of CTEs and subqueries [closed]

WITH t1 AS (SELECT a.id, AVG(standard_qty) std_avg, AVG(poster_qty) pos_avg, AVG(glossy_qty) gloss_avg FROM accounts a ...
2
votes
1answer
373 views

Deduplicate Data Using Having with Subquery

I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward. I have this ...
1
vote
1answer
1k views

Why does `row_to_json(…)` accept a sub-query?

I recently encountered this example (via a tutorial and other answer): SELECT row_to_json(x) FROM (SELECT id, title FROM my_table) x; I'm confused about the use of row_to_json(x). I thought it ...
0
votes
1answer
250 views

MS Access M:N Relationship query: exclude records with more than the requested values

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here: Let's ...
1
vote
1answer
55 views

conversion of a non correlated sub query to a join statement

Is it possible to get rid of the sub query and convert the entire query into a JOIN query? SELECT student_id, COUNT(attendance) FROM student_attendance WHERE attendance = 'ABSENT' GROUP BY ...
0
votes
1answer
755 views

Trying to run query and getting error: Error 1064 (42000)

I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine. I am getting this error: ERROR 1064 (42000): You have an error in your SQL ...
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
1answer
99 views

multiple insert queries after a set of with's

I am trying to have a reproducible way to populate a database with some test data. Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-...
-1
votes
1answer
27 views

Ambiguous syntax for IN clause

I am working on some select query and I found this syntax SELECT NOM FROM INGENIEUR A WHERE 7 IN (SELECT CODEPT FROM EMPLOI WHERE MLE = A.MLE) I have never seen this ...
1
vote
1answer
21 views

Join or Replace colum value based on query

I have this query: SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description FROM items as i LEFT JOIN hosts as h ON i.hostid = h.hostid AND i.type = 11 AND i....
5
votes
2answers
8k views

user parent table id in nested subquery

SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM (SELECT user_id FROM ( SELECT sender_id AS user_id ...
1
vote
1answer
337 views

Display subquery columns in result

I'm new to SQL (also my first question) and have been trying to determine the best practises and most efficient way to code my problem. I'm using mySQLWorkbench on MacOS. I'm trying to get the result ...
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. ...
0
votes
1answer
36 views

Comparing the location of values in different tables

New to SQL. Hope to find distinct pizza sold by restaurants that are located in the same area as bob (customer). Database schema: Customers(cname, area) Restaurants(rname, area) Pizzas(pizza) ...
1
vote
2answers
7k views

how to add loop in date column in sql through query? [duplicate]

If ID = 01234 and this ID has done transaction in 3 time in a month like this: ***DAY_no Balance*** 1/1/2018 5000 10/1/2018 10000 15/1/2018 ...
0
votes
1answer
18 views

Using OLAP-specs to compute selective running totals

Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
1
vote
1answer
2k views

SQL - sum columns with same name from diferent tables

I have 3 Tables: _tbl1_____ ID C1 C2 -- -- -- AA 2 3 BB 10 12 CC 20 22 _tbl2_____ ID C1 C2 -- -- -- BB 10 12 AA 2 3 CC 20 22 _tbl3_____ ID C1 C2 -- -- -- AA 0 0 ...
0
votes
1answer
146 views

unable to add limit to the subquery

its my first question, please bear me. I have following tables Item id name description itemPurchaseDetail id item_id(FK) branch_id(FK) purchaseRate date Stock id item_id(FK) branch_id(FK) ...
0
votes
1answer
49 views

Count total number of nested one to many relationships

Tables: Users, Costs, Payments Users can have many costs. Costs can have many payments I want to get information about the user AND count of all payments that they made This query returns result ...
0
votes
1answer
178 views

Access Sub Query Speed

I have three tables in Access: employees ---------------------------------- id (pk),name times ---------------------- id (pk),employee_id,event_time time_notes ---------------------- id (pk),...
3
votes
1answer
284 views

Query A if there is no B

I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first ...
0
votes
3answers
75 views

Other way to write query

I'm new to sql language and I'm learning about subqueries.. I have one question is there other way how to write that query? SELECT Employees.FirstName, Employees.LastName FROM Employees ...
0
votes
3answers
2k views

Joining two tables by the nearest match

General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
1
vote
1answer
3k views

Count up number of rows matching criteria in same table

I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each ...
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 ...
0
votes
1answer
242 views

Query to find products with all the given attributes

I have a table in mySQL to store product attributes like this: ------------------------------------ | product_id | att_val1 | att_val2 | ------------------------------------ | 1001001 | x202 | ...
0
votes
1answer
57 views

Are there any other ways to get results from Two tables without using Sub Query

I have a Customer table +--------+---------+ | Id | Name | +--------+---------+ | 1 | A | | 2 | b | | 3 | c | | 4 | d | | 5 | 3 | | 6 ...
2
votes
1answer
764 views

Reuse results from correlated subqueries in same query

I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting). distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types ...
0
votes
2answers
163 views

Mysql sub query issues

I have a table where I record a number of values: channel, cnam (both varchar) calldate (date/time) billsec (integer) disposition (varchar - either 'ANSWERED' or 'NO ANSWER' or 'BUSY') I need to ...
0
votes
3answers
78 views

Slow query with subselects

Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks. Time: 0.466s Time without sub-selects: 0.058s ...
1
vote
1answer
116 views

Compare all attributes in M:N relationship

I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet - mainly because I'm not sure how to name it correctly ;) So if there is ...
5
votes
2answers
206 views

A better way to write this query?

I have the following schema for my database (Postgresql 10): CREATE TABLE "PulledTexts" ( "Id" serial PRIMARY KEY, "BaseText" TEXT, "CleanText" TEXT ); CREATE TABLE "UniqueWords" ( "Id" serial ...
2
votes
1answer
463 views

Calculate quantity of tickets based on how many reserved using sub queries

I'm designing a database that will hold ticketing information. The 3 tables I use to track ticketing are tbl_ticket_type to the types of events an event can have tbl_ticket_count to keep track of ...
1
vote
1answer
35 views

Implementing a query, two types of strategies

Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id. For instance ...
1
vote
1answer
429 views

What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day?

I have a SQL Server 2016 temporal table query that I’m having a hard time figuring out an efficient way to write. Let’s say I have a temporal table called [Users] that each day has the total body of ...
1
vote
5answers
3k views

IN clause too slow

Ive been searching for about 1 hour and i have not found anything. I have this problem: When I do this query: select order_id from or_order@smartflex where order_id in ( select distinct numeroot ...
0
votes
1answer
92 views

Fix for slow SQL_INLINE_TABLE_VALUED_FUNCTION

My query with two UDT parameters takes 0.3 seconds but when the same code is encapsulated into an inline table valued function it takes 3.5+ seconds. I've spend days researching/attempting fixes (...
1
vote
2answers
164 views

Select last 2 records each time based on the record previously accessed records from the in Sql

I have Message table DDL CREATE TABLE [dbo].[Message]( [ID] [int] NOT NULL, [Message] [varchar](50) NOT NULL, [DateCreated] [datetime] NOT NULL, CONSTRAINT [PK_Message] PRIMARY KEY ...
2
votes
3answers
2k views

Obtaining a MIN() value in a column in a subquery

I have the two following tables: CREATE TABLE PC ( model ..., speed ..., ram..., ... ); CREATE TABLE Laptop ( model ..., speed ..., ram ..., screen..., ... );...
2
votes
3answers
93 views

Get the latest change in value from a streak of values

I'm looking for a SQL query which returns the most recent change (date) in a time series of sensor data. Basically I need to know when the sensor with id==i had a value change for the first time from ...
4
votes
3answers
388 views

Choosing Consecutive Seats by Seatrow

SELECT TOP N seats.*, eventtickets.eticketprice from seats INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype WHERE ((Seats.seatType ='BOXSEAT') AND seatID not in (select seatID ...
4
votes
1answer
413 views

How to get all records from one table and see if they are used in another without a sub query

I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple ...
1
vote
1answer
61 views

Updating column with multiple sub-queries

I have created the following query which is used to calculate a total from the results of a spatial query: SELECT SUM(med_price) FROM (SELECT ST_INTERSECTION(a.geom, (SELECT ST_SETSRID(geom, ...
1
vote
1answer
215 views

How to make this nested query :-

I've a table called foo which stores the child and parent information of some entity called bar, with level information as well. If there is no parent of bar, then in the parent_id column it will ...
2
votes
1answer
371 views

Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [closed]

I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think): with d AS( SELECT ...