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.
3
votes
3answers
220 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
....
...
1
vote
2answers
114 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 ...
3
votes
2answers
219 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
votes
2answers
2k 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.priority ASC, LIMIT 1) AS special_price,
( SELECT ps.date ...
3
votes
1answer
2k 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 ...
2
votes
2answers
127 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 ...
4
votes
3answers
238 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 ...
5
votes
4answers
1k 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 ...
1
vote
2answers
180 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, ...
1
vote
0answers
284 views
time interval period in sql [closed]
How to display the time period of the most widely in use in ms sql server
I have purchase data, such as:
hours the number of purchases
10:00 2
12:00 4
13:00 5
14:00 1
...
3
votes
1answer
2k 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:
...
6
votes
3answers
411 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 ...
5
votes
3answers
446 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
votes
3answers
4k 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 ...
