Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use result of an subquery multiple times into an query

A MySQL query needs the results of a subquery in different places, like this:

SELECT COUNT(*),(SELECT hash FROM sets WHERE ID=1) 
     FROM sets 
     WHERE hash=(SELECT hash FROM sets WHERE ID=1) 
           and XD=2;

Is there a way to avoid the double execution of the subquery (SELECT hash FROM sets WHERE ID=1)? The result of the subquery always returns an valid hash value. It is important that the result of the main query also includes the HASH.

First I tried a JOIN like this:

SELECT COUNT(*), m.hash FROM sets s INNER JOIN sets AS m
     WHERE s.hash=m.hash AND id=1 AND xd=2;

If XD=2 doesn't match a row, the result is:

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | NULL | 
+----------+------+

Instead of something like (what I need):

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | 8115e| 
+----------+------+

Any ideas? Please let me know! Thank you in advance for any help.

//Edit: finally that query only has to count all the entries in an table which has the same hash value like the entry with ID=1 and where XD=2. If no rows matches that (this case happend if XD is set to an other number), so return 0 and simply hash value.

like image 334
The Bndr Avatar asked Apr 20 '11 13:04

The Bndr


People also ask

How do I return multiple values from a subquery?

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery.

Can you use 2 subqueries in a SQL query?

A subquery is a complete query that appears in the WHERE or HAVING clause of an SQL statement. You can specify up to 16 subqueries within a single SQL statement, and you can specify subqueries within a subquery.

Can a subquery be multi valued?

A multi-value subquery retrieves more than one value and has two forms of syntax, as shown below. SELECT ...

Can we use multiple subqueries in single parent query?

We can use single-row subqueries for returning single rows, multiple-row subqueries for returning multiple rows, correlated subqueries for returning multiple columns depending on the parent query, and nested subqueries to return a subquery to another one.


2 Answers

SELECT  SUM(xd = 2), hash
FROM    sets
WHERE   id = 1

If id is a PRIMARY KEY (which I assume it is since your are using a single-record query against it), then you can just drop the SUM:

SELECT  xd = 2 AS cnt, hash
FROM    sets
WHERE   id = 1

Update:

Sorry, got your task wrong.

Try this:

SELECT  si.hash, COUNT(so.hash)
FROM    sets si
LEFT JOIN
        sets so
ON      so.hash = si.hash
        AND so.xd = 2
WHERE   si.id = 1
like image 156
Quassnoi Avatar answered Oct 25 '22 13:10

Quassnoi


I normally nest the statements like the following

SELECT  Count(ResultA.Hash2) AS Hash2Count,
        ResultA.Hash1
FROM    (SELECT S.Hash AS Hash2,
                (SELECT s2.hash 
                 FROM   sets AS s2 
                 WHERE  s2.ID = 1) AS Hash1
            FROM sets AS S
            WHERE S.XD = 2) AS ResultA
WHERE ResultA.Hash2 = ResultA.Hash1
GROUP BY ResultA.Hash1

(this one is hand typed and not tested but you should get the point) Hash1 is your subquery, once its nested, you can reference it by its alias in the outer query. It makes the query a little larger but I don't see that as a biggy.

like image 32
John Petrak Avatar answered Oct 25 '22 14:10

John Petrak