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
1answer
60 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 ...
1
vote
2answers
137 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) ...
0
votes
0answers
16 views

SQLite3 query question [on hold]

I'm using the Ghost blogging platform (https://ghost.org/ its very nice) and I'm trying to make an external site, which accesses its database. The relevant tables, and their relevant columns for my ...
3
votes
1answer
69 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
50 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
72 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, ...
2
votes
1answer
94 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
35k 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
59 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
61 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" ...
0
votes
0answers
20 views

Select ID put into a new Table - PostgreSQL 9.2

I need to select all the File ID's on a table and put the results in the new table I've created. How can I do that? Please, have a look: 1: Select all the file ID's of gorfs.inodes/segments for ...
1
vote
2answers
75 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!) ...
0
votes
1answer
145 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, ...
0
votes
2answers
236 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, ...
1
vote
2answers
85 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
30 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
81 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
33 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
64 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
43 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
165 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
45 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
25 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
22 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
22 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
74 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
11 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
69 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
46 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
37 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
22 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
82 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 ...
5
votes
4answers
28k 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 ...
4
votes
2answers
57 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
8k 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
81 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
250 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` >= ...
0
votes
3answers
59 views

Updating rows from other rows in a huge table

I have a huge table with items item-id | name | type | extra Many of them are "old versions" of type 0. I have to update the extra field in all new items (same item-id, but type > 0) with the ...
1
vote
2answers
95 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 ...
2
votes
1answer
72 views

MariaDB subquery cache scope and settings

I am migrating an application from MySQL 5.6 to MariaDB 10. One major new feature of MariaDB is the subquery cache. I am trying to determine the expected effect this cache will have on our ...
15
votes
3answers
319 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
0
votes
0answers
74 views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
1
vote
1answer
42 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
0
votes
0answers
36 views

MySQL - SELECT Columns from results of another Query [duplicate]

I'm trying to use a dynamic range of columns fetched from another query, specifically I want the results of this query: SELECT COLUMN_NAME as DYNAMIC_COLUMN FROM information_schema.columns WHERE ...
1
vote
2answers
76 views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
2
votes
2answers
166 views

PostgreSQL query very slow when subquery added

I have a relatively simple query on a table with 1.5M rows: SELECT mtid FROM publication WHERE mtid IN (9762715) OR last_modifier=21321 LIMIT 5000; EXPLAIN ANALYZE output: Limit ...
2
votes
1answer
39 views

Problem to select not in

I want to display the values that are between 0 and 10 and dont exist in the current table For example : Field 1 2 3 5 9 8 Result expected: 7 4 The problem is that the column field is a ...
8
votes
1answer
96 views

How can I get the hierarchical values from the below query?

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID. I need to get all the category IDs and their subcategory ...
10
votes
1answer
244 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...