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.

learn more… | top users | synonyms

1
vote
2answers
28 views

SQL Server — More efficient alternatives to repeating an (almost) identical subquery

I need to improve the efficiency of multiple subqueries within a WHERE clause. The context is a report generator on an existing product. The only input I can provide is the WHERE clause. I have 3 ...
4
votes
2answers
359 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
0
votes
4answers
51 views

How can I use MySQL variables in subqueries?

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why. Here's the query: SELECT * FROM animals WHERE name IN(SELECT ...
0
votes
1answer
55 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...
6
votes
5answers
33k views

Get multiple columns from a select subquery

SELECT *, p.name AS name, p.image, p.price, ( SELECT ps.price FROM product_special ps WHERE p.id = ps.id AND ps.date < NOW() ORDER BY ...
2
votes
2answers
165 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
1
vote
2answers
109 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
0
votes
1answer
170 views

Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this: select datetime, PLANT_STATUS, ...
12
votes
2answers
542 views

How can SQL Delete using a sub query

The following code was added by one of our developers to delete duplicate records from the table: DELETE SubQuery FROM ( SELECT ID ,FK1 ,FK2 ...
0
votes
2answers
145 views

Are these two queries technically equal to each other?

Ran it in my query browser. I was am getting same results for both. But still need a confirmation. SELECT cs.RSCode , CustomerName , em.EmailId FROM Customer_STN cs JOIN ...
0
votes
0answers
42 views

Moving slow sub-query to With clause

I am trying to optimize this query : SELECT sum(a-alias.s_count) AS count FROM a-table a-alias WHERE ( a-alias.created_at_minute_id >= (floor(extract(epoch from( select ...
1
vote
1answer
40 views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
1
vote
2answers
96 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
0
votes
2answers
43 views

Does subqueries “short-circuit” based on other conditions?

I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can ...
-1
votes
1answer
47 views

Alter - Subpartition a partitioned table

I have a table which is partitioned by RANGE on DATE key CREATE TABLE T1 DOC_ID VARCHAR NOT NULL. STATE VARCHAR ... DOC_DATE ... PARTITION BY RANGE (DOC_DATE)( PARTITION feb16 VALUES LESS ...
5
votes
1answer
88 views

Sub-select takes “ages” - EXCEPT much faster

Script to create the tables DROP TABLE IF EXISTS history; CREATE TABLE history ( id integer NOT NULL, ticket_id integer NOT NULL); ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
1
vote
2answers
68 views

Is there a way to do the following query with joins instead of a sub query?

I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view. The only thing similar to my problem is the greatest-n-per-group queries but ...
3
votes
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
4
votes
3answers
191 views

Correlated Subquery in SQL Server 2014

I am running a correlated subquery to find out the listing of vendors (by Vendor Name) that are in different cities, states, i.e. we want to know the vendors that do not have a common city and state ...
0
votes
3answers
294 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, ...
0
votes
3answers
50 views

Optimise MySQL SELECT with LEFT JOIN subquery

I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds. SELECT s.siblings, l.id FROM `list` l ...
6
votes
3answers
340 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
2
votes
0answers
40 views

Classification of Subqueries?

Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories: Scalar: returns a single value Table-valued: Returns a table, to compare or ...
7
votes
2answers
211 views

Correlated Subquery SQL Server 2014

I am (unsuccessfully) using a correlated subquery on an Invoices table: Invoices(InvoiceID, VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ), to find the sum of largest unpaid invoices by ...
1
vote
1answer
87 views

Commands out of sync; you can't run this command now

I have a query which was working fine a few days back: SELECT id, title, details, filetype, filepath, size, details, location, datetime, IF(hid=1, 'Anonymous', ( SELECT ...
0
votes
1answer
41 views

mysql update based on select result

Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them. Update update customer_entity set website_id = 4 where website_id = 3 Select ...
1
vote
2answers
89 views

Referencing a SELECT subquery value in the WHERE clause

Complete query SET @vSelect = ' SELECT T1.BranchShortName AS BranchShortName, ( SELECT TT1.EmployeeName FROM ...
0
votes
0answers
34 views

Need hep with a query to retrive data from realtional database

I am having a database for blog. I want to retrieve posts by specific condition (i.e by location and category). The catch is both location and category are present in a single table called terms. This ...
3
votes
1answer
91 views

Search between dates where all dates appear

I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate. Tables columns are: id house_id date status house_id + date are unique the closest I could ...
0
votes
1answer
20 views

Can we use subquery in GRANT statement in postgres

I want to grant select privilege on few selected tables. Can I use sub-query in GRANT statement???? I have tried the following query: GRANT SELECT ON TABLE (SELECT array_to_string( ...
1
vote
2answers
52 views

How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

This query: select distinct profile from dba_profiles; Returns 3 rows: default default1 default2. I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query ...
0
votes
1answer
76 views

Why are correlated subqueries sometimes faster than joins in Postgres?

This relates to the dataset described in Postgres is performing sequential scan instead of index scan I've started working on adapting the import logic to work with a more normalised schema – no ...
3
votes
1answer
72 views

Subquery in MS Query doesn't work

I'm a beginner in SQL. I hope my question is not too stupid. I'm trying to write a query in MS Query and want to see the results in Excel. The problem is that MS Query doesn't accept my code. I get ...
0
votes
1answer
56 views

Is there a way to used grouped data multiple times in a query

Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments. The solution that I ...
2
votes
1answer
102 views

How to select a subset from a query result

I have a query which calculates the Time Difference and Distance Between the Next and Previous Row: SELECT * FROM ( SELECT iVehicleMonitoringId AS PreviousId, ...
1
vote
1answer
119 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
8
votes
2answers
40k 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 ...
3
votes
3answers
3k views

Query to find GUID's “not in” null containing subquery returns no results

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
1
vote
3answers
68 views

Return 2 columns from MySQL subquery?

I'm new in MySQL programing and I have one problem. I created this query: SELECT *, topics.createdate AS TopicCreateDate, (SELECT (SELECT username FROM users ...
4
votes
1answer
98 views

Subquery can't find column from superquery's join

I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" ...
1
vote
2answers
83 views

Effect of where statement in subquery (to get rid of unnecessary join)

Please consider the following query: select * from parent p where p.id in ( select p.id from parent p inner join child c on c.parentId = p.id where p.age > 50 ) Is it safe (in general!) ...
1
vote
2answers
90 views

simple SELECT EXISTS takes about two hours to execute

I have mysql server hosted. Now, If I issue simple SQL statement SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND ...
0
votes
1answer
35 views

Understanding the given correlated subquery

I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks} Select Sname from Student s1 where (select count (*) from Student s2 where ...
4
votes
2answers
86 views

How can i return the max of a sum by each team?

I have a lot of teams and i want to know the maximum of sum total of each team. This's my query : SELECT campaign_id, campaign_identifier, team, campaign_name, ...
4
votes
1answer
43 views

MySQL query returns different result on second execution

I'm not too familiar with MySQL variables but I'm trying to avoid redundant sub selects while calculating these results. The first execution only returns 1 of the correct calculations. The second ...
2
votes
1answer
69 views

Join query returning 0 rows - impossible where

My query is performed from a php web request using a PDO prepared statement. My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order ...
1
vote
1answer
44 views

Return the lowest value

How do I go about getting the course number (CNO) of all the lowest enrolled by department? For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it ...
3
votes
1answer
175 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
59 views

Single MySQL query with row averages based on conditions

I have problem, where i know how to proceed with multiple queries, but dont really now I Have a table with BP { name: varchar systolic : int diastolic : int timestamp: date } I need ...
1
vote
1answer
26 views

Help writing a query that select the min column in a group by

I have two simple table CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, nickname varchar(35) NOT NULL, PRIMARY KEY (id), ); CREATE TABLE user_service ( id INT NOT NULL AUTO_INCREMENT, ...