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.
1
vote
2answers
219 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 ...
0
votes
2answers
47 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
2
votes
1answer
62 views
Best practice: Unions or a derived table?
I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:
CREATE TABLE `pending` (
...
`invoice` int(11) DEFAULT NULL,
`lid` int(11) DEFAULT ...
2
votes
1answer
146 views
Does MySQL have problems with nested insert like with subqueries in where?
Query 1:
INSERT `personal`.`locations`
SELECT DISTINCT `s`.*
FROM `references` `t`
JOIN `locations` `s` ON `first_id` = `s`.`id`
WHERE
`lat` >= 37.3
AND `lat` <= ...
0
votes
1answer
96 views
MySQL subqueries that use range based on values of main queries don't use indices properly
I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.
Ok so my findings are that a normal query that runs very ...
0
votes
0answers
99 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
0
votes
0answers
101 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
