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.
716 questions
4
votes
3
answers
50k
views
How to select/count rows where the same value exists in a column but not in other column all at the same table
I have this table structure and data:
CREATE TABLE IF NOT EXISTS `default_relations_users` (
`id_user_rq` int(11) NOT NULL,
`id_user_ap` int(11) NOT NULL,
UNIQUE KEY `rusers_rq_ap_idx` (`...
- 1,888
2
votes
2
answers
2k
views
pull records that are inactive
I need help with this query. I have records in customer table
Main table - M
+---------+---------+----------+
| company | Account | Active |
+---------+---------+----------+
| A | A123 | ...
- 783
31
votes
2
answers
49k
views
Subqueries' aliases same as main queries' aliases
I have an SQL query whose aliases are the same as some of its subquery's aliases.
For example:
select *
from ROOM r
where ...
(
select *
from ROAD r
...
- 517
5
votes
3
answers
2k
views
How can I select using the same fields from an undetermined number of tables using MySQL?
I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:
foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
...
- 173
1
vote
2
answers
162
views
How to restructure this slow query
For the following query:
SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u....
- 510
3
votes
2
answers
1k
views
MySQL cache is not getting hit by subqueries
I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.
Let's say I have an item table
ID | Item
-----------
1 | Item_1
2 | Item_2
3 | ...
- 2,248
54
votes
8
answers
230k
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 ps....
- 633
6
votes
1
answer
8k
views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
- 165
2
votes
2
answers
1k
views
Order by sub select
I have 3 tables: articles, paragraphs, notes
An article may have many paragraphs, and a paragraph may have 0 or 1 note.
Now I want to find all notes belong to an article:
select * from notes
where ...
- 325
6
votes
3
answers
384
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 ...
- 3,283
13
votes
4
answers
21k
views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
- 395
1
vote
2
answers
695
views
Get the most recently completed step for each SQL job
I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:
SELECT TOP 1 j.job_id, j.name, h....
- 10k
8
votes
1
answer
13k
views
Query performance with subquery and IN clause
I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries:
...
- 183
11
votes
3
answers
4k
views
SQLITE: A problem of tags and products
I'm searching for a way to create a query to do the following:
Let's consider 3 tables:
products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product
Let's ...
- 213
5
votes
3
answers
2k
views
What could be the reason for disallowing a sub query in the values clause?
For example
SQL> create table dates(d date);
Table created.
SQL> insert into dates select sysdate from dual;
1 row created.
SQL> select * from dates;
D
---------
28-MAY-11
SQL> ...
- 3,361
4
votes
3
answers
17k
views
How to convert a Top 1 subquery using outer table alias to Oracle?
I have the following SQL Server query
select
(select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc,
a1,
a2
from AA a
where a2 = 2;
which I can rewrite using analytic functions
...
- 12.3k