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

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

If I understand correctly what you are trying to get, query should look like this:

select count(case xd when 2 then 1 else null end case), hash from sets where id = 1 group by hash

Answer 4

I agree with the other answers, that the GROUP BY may be better, but to answer the question as posed, here's how to eliminate the repetition:

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