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

0
votes
2answers
45 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
49 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
96 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
69 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
210 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
337 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, user....
0
votes
3answers
128 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
345 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
43 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
220 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
583 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
51 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
91 views

Referencing a SELECT subquery value in the WHERE clause

Complete query SET @vSelect = ' SELECT T1.BranchShortName AS BranchShortName, ( SELECT TT1.EmployeeName FROM [dbo].[...
0
votes
0answers
37 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
126 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
32 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( array_agg(...
1
vote
2answers
61 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
80 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
79 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
130 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
142 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
45k 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
75 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
153 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
95 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
109 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 PRODUCT_ID='2bc7277f-c7c3-...
0
votes
1answer
37 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 s1.marks<=s2.marks)...
4
votes
2answers
88 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, Month(...
4
votes
1answer
59 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
73 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
181 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
60 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, ...
0
votes
0answers
27 views

How to use COUNT() properly with a 3 table join

I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) ...
0
votes
0answers
46 views

How to pull sales for every single month based on my query below using ORACLE

So I am working on a report right now that pulls customer information, and sales for a particular date range, who used a PROMOTION CODE. My current query pulls all the data I need and gives me a sum ...
0
votes
3answers
90 views

Select data from single table using Self join

I have requirement to select a data from single table. The requirement is as follows Select data from table which has one value but not another. for e.g I want to select distinct ID which has value 1 ...
0
votes
0answers
14 views

Microsoft Access Address Book Form Query

I have downloaded the Microsoft Access template for an address book. I am looking at the contact form and it allows you to query the db and pull back a record and all the details in the row. What I ...
0
votes
2answers
80 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
0
votes
1answer
75 views

get all the count of sub category from the product table in single query with mysql optimization [closed]

Table structure: CREATE TABLE some_table ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `Product_model` varchar(255) NOT NULL, `product_discount` ...
0
votes
1answer
57 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
0
votes
0answers
24 views

Is it possible to stack similar columns from a subquery and join the other unique columns in one query?

I am fairly new to the sql world and have been struggling with a query. I have two subqueries that are generating tables with two columns. The first table is part # and model_countH. The second table ...
2
votes
2answers
100 views

Query with WHERE NOT and NOT IN with subquery

MySQL Version: 5.6.27 I am using the following two tables TABLE: oc_product product_id int(11) model varchar(64) tax_class_id int(11) and TABLE: oc_product_to_category ...
4
votes
2answers
76 views

Join subquery by year and month

I want to collect data from differed tables grouped by year and month. I'm using subquery for each sub data. Using mysql. SELECT YEAR(td.db_date) AS stat_year, job.total_jobs FROM ...
3
votes
1answer
11k views

Writing a query to find all employee salaries that are higher than their managers

I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries. I can isolate ...
0
votes
1answer
84 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly ...
2
votes
1answer
293 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...