[RESOLVED] Multiple table query problem?

Pluto

Hi there,

I'm having a real problem figuring out how to deal with this query. In short, I've got two tables

!table1
column -- id, id2
row1 -- 1, 2
row2 -- 1, 3
row3 -- 2, 4

!table2
column -- id, id2, state
row1 -- 1, 1, on
row2 -- 1, 2, off
row3 - -1, 3, standby

I have tried using a subquery which looks like this...

$query = "SELECT state FROM table2 WHERE id2 = ( SELECT id2 FROM table1 WHERE id = '1')" ;

In theory this subquery pulls out values "2 and 3" then the main query should pull out "off and standby", but instead... I get "Unable to save result set ** Subquery returns more than 1 row"

Any help would be greatly appreciated.

NogDog

Try using "IN" instead of "=" just before the subquery.

Roger_Ramjet

Or just do it properly with a JOIN - far more eficient.

SELECT table2.state FROM table1 INNER JOIN table2 ON table1.id2=table2.id2 WHERE table1.id = 1

guessing at the table relationships here

Pluto

Thanks NogDog & Roger Ramjet, I ended up using the JOIN example and it worked a treat!

$result = mysql_query("SELECT table2.state FROM table2 INNER JOIN table1 ON table1.id2=table2.id2 WHERE table1.id = 1") or die(mysql_error());  

while($row = mysql_fetch_array( $result )) {
	echo $row['state'];
}