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.

Must be very sensitive if the table name alone can give it away. :) — GolezTrol
– GolezTrol, Commented Dec 20, 2011 at 21:03

Michael Berkowski · Accepted Answer · 2011-12-20 21:03:39Z

5

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

answered Dec 20, 2011 at 21:03

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

just figured it out :). Thanks. I'll mark as correct answer in two minutes (rules here)

GolezTrol · Accepted Answer · 2011-12-20 21:04:47Z

2

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.

answered Dec 20, 2011 at 21:04

GolezTrol

116k19 gold badges186 silver badges215 bronze badges