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.
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.
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.
A multi-value subquery retrieves more than one value and has two forms of syntax, as shown below. SELECT ...
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With