Alex Rivera | Logout

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

Asked 2011-04-20T13:22:32.373
14

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.

Edit
Report

1 Answer

1

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.

answered 2011-04-20T14:08:15.407

Your Answer