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
1answer
21 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, (SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p.id) AS postlock_id, (SELECT COUNT(rn.id) FROM ...
0
votes
1answer
35 views

Accessing computed column in a nested query

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer. In ...
0
votes
0answers
22 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
2
votes
2answers
172 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) ...
2
votes
2answers
52 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
2
votes
3answers
60 views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT ...
0
votes
1answer
174 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, ...
3
votes
3answers
46 views

Query to find missing status events

I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history. Right now, I can identify records from main with no status_history at all, but I need to ...
1
vote
2answers
113 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 ...
1
vote
2answers
38 views

Does the EXIST clause only return distinct rows by its nature?

I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database. The first query uses an EXISTS clause with a sub query. ...
1
vote
1answer
28 views

MySql LIMIT + JOIN is stopping data from being returned?

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases! I've got two tables and am trying to return all profiles that are assigned ...
3
votes
1answer
66 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
4
votes
1answer
41 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
34 views

SQL Query with double distinct dates iteration, and start date < my date < final date

I have a kind of rental system database where user can rent an entire house, or just a room of the house. I have a table called offers which has columns id, room_id and a few more. If room_id = ...
0
votes
1answer
29 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, ...
0
votes
2answers
36 views

Error When Trying to Run Subquery

I am having issues getting my subquery to run properly. Everything runs quickly and correctly until I add the subquery. I get the error: "Msg 4145, Level 15, State 1, Line 66 An expression of ...
1
vote
2answers
34 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
380 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
71 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
58 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
34k 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 ...
12
votes
2answers
547 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
146 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
43 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
97 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
48 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
91 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
197 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
312 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
82 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
41 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
217 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
156 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
44 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
90 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
36 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
105 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
23 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
53 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
77 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
73 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
57 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
108 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
126 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
41k 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 ...