MySQL query nested query

Question

I`m trying to run a nested query on MySQL (phpmyadmin) and via PHP, and both result in the same output which is incorrect.

NOTE: Table name have been clipped due to sensitivity of project

SELECT * FROM `table1` WHERE ID="SELECT `field1` FROM `table2` WHERE ID=1"

This returns zero rows, although each query alone gives a valid output as below

SELECT `field1` FROM `table2` WHERE ID=1

Gives the required output, and this output when used in the first part of the main query provides also what is required. Please help.

Answer 1

Your nested query is wrong, it should look like this:

SELECT * FROM `table1` WHERE ID in (SELECT `field1` FROM `table2` WHERE ID=1)

In your case, you're comparing table1.ID with a string containing the second query.

Answer 2

Don't enclose it in quotes. Instead enclose it in parentheses:

SELECT * FROM `table1` WHERE ID=(SELECT `field1` FROM `table2` WHERE ID=1)

If multiple rows are expected from the subquery, use WHERE ID IN (SELECT...) instead of WHERE ID=(SELECT...)

You'll probably get better performance with a JOIN though:

SELECT table1.* 
FROM
  table1 JOIN table2 ON table1.ID = table2.field1
WHERE table1.ID = 1