Skip to main content

Return to Question

added 261 characters in body
Source Link
The Bndr
The Bndr
  • 13.4k
  • 16
  • 73
  • 110

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.

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.

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.

fixed error at query "and ID=1 and XD=2;" was wrong. right is: "and XD=2;", sorry
Source Link
The Bndr
The Bndr
  • 13.4k
  • 16
  • 73
  • 110

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 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.

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 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.

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.

added 3 characters in body
Source Link
Donut
Donut
  • 113.5k
  • 20
  • 136
  • 147

an mysqlA MySQL query needs the results of ana subquery onin 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 ID=1 and XD=2;

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

First iI tried an JOINa 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 matched anmatch a row, the result is:

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

Instead offof something like (what iI need):

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

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

an mysql query needs the results of an subquery on 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 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 an 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 matched an row, the result is:

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

Instead off something like (what i need):

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

Any ideas? So please let me know! Thank you! Thank you.

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 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.

added 2 characters in body
Source Link
hsz
hsz
  • 152.8k
  • 63
  • 268
  • 320
Source Link
The Bndr
The Bndr
  • 13.4k
  • 16
  • 73
  • 110