SELECT problem this time

[OhLordy]

I think this is going to start being a daily post. Although I'm sure my SQL skills will improve.

This is the syntax of my query

SELECT name FROM w_jewellery_manufacturer WHERE jmanref_id=(SELECT jmanref_id FROM w_jewellery_series WHERE jsref_id='3')

and this is the error I'm getting

You have an error in your SQL syntax near 'SELECT jmanref_id FROM w_jewellery_series WHERE jsref_id='3')' at line 1

I've looked through it a couple of times and can't find the problem although I haven't ever done nested queries before.

Thanx Rob

[rIkLuNd]

Try using two queries instead:

$query_1 = "SELECT jmanref_id FROM w_jewellery_series WHERE jsref_id='3'";
$result_1 = mysql_query( $query_1 ) or die( mysql_error() );
$data = mysql_fetch_array( $result_1 );

$query_2 = "SELECT name FROM w_jewellery_manufacturer WHERE jmanref_id='" . $data[ "jmanref_id" ] . "'";
$result_2 = mysql_query( $query_1 ) or die( mysql_error() );

[OhLordy]

I know I can do it with two queries but I have a book here that says I can do nested queries. So if I can I'd like to be able to do everything in one database call.

[xblue]

mysql version 4 will support subselects, 3.x doesn't.

[OhLordy]

Solved it with a LEFT OUTER JOIN

SELECT w_jewellery_manufacturer.name 
FROM w_jewellery_series LEFT OUTER JOIN w_jewellery_manufacturer 
ON w_jewellery_series.jmanref_id=w_jewellery_manufacturer.jmanref_id 
WHERE jsref_id='3'

It's a bit of a mouthfull though.
Thanx Again Guys
Rob

PS just because I'm interested. I'm using version 4.0.10-gamma-standard so it should work right? Unless there's an error in the syntax.