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