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
48 views

Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data

I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new ...
0
votes
1answer
58 views

select subqueries with join

I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds. Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName, Coalesce(Error1, '') + CASE WHEN Error1 is ...
3
votes
2answers
5k views

Why does COALESCE function not work for this query?

I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can ...
-1
votes
2answers
1k views

Subquery returns more than one row, any doesn't help

select first_name, middle_name, last_name from students where student_id = any (select student_id from entries where subject_id = (select subject_id ...
2
votes
2answers
769 views

Slow CREATE table from subquery using SELECT .. INNER JOIN

This query takes ~90 seconds: CREATE TABLE filtered ( SELECT DISTINCT f2.cityCode, f2.productCode, f2.productDesc, f2.inventoryDate, f2.inventoryLevel FROM etm ...
2
votes
1answer
711 views

Correlated Subqueries: resolving ambiguous names

Suppose I have 2 tables which share some column names. In this case the primary key is both is called id. CREATE TABLE artists( id int primary key, name text, -- …, ); CREATE TABLE ...
1
vote
1answer
86 views

SQL multiple select with where conditions performs really poorly

Data structure is something like this: Number| ID | DateTime | Status 50000001, 101, 1/1/09 0:10, PO 50000001, 110, 1/1/09 0:11, PO 50000001, 102, 1/1/09 0:15, PO 50000001, 101, 1/1/09 0:10, PCK ...
4
votes
1answer
15k views

Passing a value to a nested subquery

I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results: SELECT elements.id, [ ... ], (SELECT COALESCE(craft_w8_a.weight, 0) +...
2
votes
2answers
493 views

Overcome SubQuery Returned More Than 1 Result

I found this syntax already answered to a diff question, but I am getting the error of More than 1 result returned what do I need to alter in my syntax in order to defeat this error? Declare @ONE ...
0
votes
1answer
258 views

Optimize multiple group_concat

Just wondering if this query could be optimized or if sub-queries would be better? select t.entry_id, t.title, d.field_id_249 as unique_id, d.field_id_577 as keywords, d.field_id_681 AS video, ...
1
vote
2answers
219 views

PostgreSQL: windowing to replace multiple sub-queries … hopefully

I have two tables: CREATE TABLE fund_data ( id serial NOT NULL, fund_entries_id integer NOT NULL, fund_val numeric(25,6) NOT NULL, bbg_pulls_id integer NOT NULL, CONSTRAINT fund_data_pkey ...
0
votes
2answers
299 views

How to query with the condition with subquery [closed]

I have two tables table A and table B. table B has a field called condition (where some sql conditions are stored). Now i wanted to query like this select * from table_A as a where a.id = 1 ...
2
votes
1answer
10k views

Correlated sub-query on the same table with only one alias

I have query which look like this SELECT * FROM my_table WHERE ... AND id IN (SELECT t2.id FROM my_table t2 WHERE id = t2.id AND ... ); I know in this ...
2
votes
2answers
4k views

Debit Credit Balance calculating from previous records with Pagination

I have a table which contains the following data: ID In Out 1 100.00 0.00 2 10.00 0.00 3 0.00 70.00 4 5.00 0.00 5 0.00 60.00 ...
0
votes
1answer
100 views

How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps. Here are the schema ...
0
votes
1answer
560 views

Find first-time purchases with certain category

I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I ...
1
vote
2answers
55 views

How can I use all the selected values of a column, one by one, in another query?

Use column values returned from the statement below: SELECT DISTINCT(column_name) FROM Table1; into the following query: SELECT dif_col_1, dif_col_2 FROM Table2 WHERE key_in_table = (...
1
vote
0answers
30 views

Getting Available Date/Times For Attendees And Avoid Conflict

+------------+ +---------------+ +--------------+ | attendees | | attendee_times| | meeting_times| +------------+ +---------------+ +--------------+ | attendee_id| | at_id | | ...
0
votes
1answer
64 views

Missing date handling between subquery and OUTER APPLY

I have two sub-queries to get visit date and visit type that I combined into one outer apply. The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want. ...
0
votes
1answer
555 views

Why MySQL performs full table scan if subquery is used in update statement

I have a test table: create table t1 ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `description` varchar(120) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; ...
2
votes
2answers
1k views

Postgres optimizing nested exists

I have a table of events that each take place during a time range, and I'm trying to create a template for a query which can find events that take place within a specified time period of correlated ...
3
votes
2answers
8k views

conditional subquery

I have the following query: SELECT id, email, first_name as "firstName", last_name as "lastName", is_active as "isActive", password, access, CASE WHEN access < 3 THEN ( ...
0
votes
1answer
4k views

MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

I am joining together three tables in order to display the names of recommended providers for a property. First I join my properties (alias p) table to the properties_recommended_providers table ...
3
votes
2answers
513 views

LIMIT of rows but with complete “sets”

I guess this is a pretty tough one. I want to get results from the database, per 5 rows max (so a limit of 5 each time), but each time I don't want to return incomplete "sets" either. Look at my table ...
1
vote
2answers
55 views

How to query Wikipedia's database to retrieve the main topic and the surface forms of a given title?

First, Wikipedia's database scheme diagram is available here. I have used one of their dumps locally to work with it. Now, I need a single MySQL query to check both page and redirect tables with a ...
2
votes
1answer
110 views

Slow subquery in select join

I have a slow subquery as below (taking 2 min 35.03 seconds) SELECT * FROM erp_gl_batch_item i LEFT JOIN erp_gl_batch b ON i.gl_batch_id = b.gl_batch_id LEFT JOIN erp_customer c ON i....
5
votes
1answer
419 views

How can I identify correlated subqueries that can not be rewritten as JOINs without using DISTINCT?

I am fairly new to SQL query tuning. I have been trying to understand how to write equivalent queries. While going through Prof. J. Widom's Stanford online video lectures, she makes mention that some ...
-3
votes
1answer
74 views

multiple row selection to a single row [closed]

I'm a beginner. 4 tables and result required is given below . I'm able to achieve the result partially with this query. (getting only uat date , not all 3) Please help to achieve entire result .. ...
1
vote
1answer
41 views

Subquery check for every occurence - Gantt style

I am storing a sort of gantt chart into my MySQL database and want to retrieve the "current" tasks for each "case". One case is translated as one project. The schema will help a lot so here is the ...
0
votes
1answer
209 views

SELECT same columns of Foreign table multiple time

I have tables as follows: ORG id Name media_budget p_contact s_contact ------ ------- ------------ --------- ----------- 1 FIFA 4654623 1 3 2 ...
5
votes
1answer
2k views

Why does adding ROWNUM to a query improve performance?

I have two queries: 1) This query has a ROWNUM column (takes 20 seconds to execute): SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y ...
0
votes
1answer
1k views

Use ANY/ALL to retrieve customer who placed the highest number of orders

I have this tables, and i would like to retrieve the customer who placed the highest number of orders. i wrote this query: select * from customers where cust_id=( select cust_id from orders group by ...
1
vote
2answers
57 views

Trying SQL query with subquery in WHERE clause

For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one. Now, we have linked all ...
1
vote
1answer
445 views

MySQL subquery with IN operator optimization

I have a MySQL query in which I try to get articles with keywords matching those of a given page and that are not already posted to it. The db tables are : items : Articles table. features : ...
1
vote
1answer
44 views

How to improve my mysql query execution performance

I have a main table named prog as below: CREATE TABLE `prog` ( `prog_id` int(11) NOT NULL AUTO_INCREMENT, `prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL, `prog_edit_date` varchar(...
2
votes
1answer
997 views

How to limit an inner query by outer query attribute

I have a requirement to sort a table by multiple criteria, select the first and associate with another table. The DB is Postgres. Let's say we have players and teams where player has played from ...
4
votes
1answer
333 views

Why is it examining 2.2 billions rows for this delete query with a subquery?

We use the community software Invision Power Suite 4.1.15. The community has 15+ million posts and we've set up the search index table it has to contain a year of posts. We discovered the following ...
6
votes
2answers
99 views

Retrieving data of vehicle drivers that have not been assigned to a job

Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display: Jobs that have not been ...
1
vote
1answer
2k views

Sub-Query group-by count(*) not returning 0

Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some ...
0
votes
1answer
38 views

Mysql query to get count result and group by

I've got three tables with different records for users, User username | realname | date evn-az-3ju john 11/2012 03:09:40 p.m. jwyvm_rdyt steve 12/2012 ...
1
vote
2answers
276 views

How to use the output of one query in other

I am using following query to get the output as JobId, JobName and Start Execution date SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ja.last_executed_step_id FROM msdb.dbo....
0
votes
1answer
133 views

Date comparsion within subquery fails with: “out-of-range value”

I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid. CREATE TABLE [dbo].[input]( [id] int, [...
0
votes
1answer
61 views

Check for existing matches to find the field their grouped-by

So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect. What I end up doing, is ...
2
votes
1answer
68 views

Comparing Row Values

I'm a SQL newbie and would like some help please. This is my table: ID Name Number Src_Call_Leg Dest_Call_Leg -- ---- ------ ------------ ------------- 1 John 5555 100 1000 2 ...
1
vote
2answers
60 views

How to sort records before inter-row calculation?

I have a time sequence data set to analyze but the difficulty is that the data set is very big. the time sequence events are committed by different objects with affiliation. its in mysql, there is no ...
2
votes
1answer
29 views

How to add the value of my Sub-query, I want to add the value of Cash,Charge and Other_Charges as Total_payment

I am using 2 tables, TL_FISCALREPORT and TL_TENDER. What I want to do is to add the total value of my 3 subqueries from TL_TENDER named as cash, charge, tender and show it in my query: SELECT ...
2
votes
0answers
268 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 ...
1
vote
2answers
2k 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 ...
2
votes
1answer
86 views

JOIN with the last id

I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better ...
5
votes
2answers
3k views

JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting ...