Data from more than 2 tables after one to many table join???

[GregL83]

Hello,

 

I have a query that joins two tables:

 

$query = "SELECT s.id, s.name, s.slug, s.overall_comments, uc1.scene_id, uc1.comment, uc1.update_time FROM scenes s, user_comments uc1 JOIN(SELECT scene_id, MAX(update_time) AS time FROM user_comments GROUP BY scene_id) AS uc2 ON uc1.scene_id = uc2.scene_id AND uc1.update_time = uc2.time WHERE ".likeQuery($keywords)." AND uc1.scene_id = s.id"

 

likeQuery is a function that returns a LIKE string for searching the particular fields... this all works fine

 

I would like to add additional tables to the mix...  Any idea how to do this?

[GregL83]

I tried this and it return zero results:

 

$query = "SELECT s.id, s.name, s.slug, ct.name, s.overall_comments, uc1.scene_id, uc1.comment, uc1.update_time FROM scenes s, cities ct, user_comments uc1 JOIN(SELECT scene_id, MAX(update_time) AS time FROM user_comments GROUP BY scene_id) AS uc2 ON uc1.scene_id = uc2.scene_id AND uc1.update_time = uc2.time WHERE ".likeQuery($keywords)." AND uc1.scene_id = s.id AND s.city_id = ct.city_id"

 

Even if I just add an additional table to the FROM sequence, mysql stops returning results... any ideas???

[fenway]

Then there are no matches in the non-leading tables.

[GregL83]

but there are...  every record in the scenes table HAS TO have a city_id that has a row in the cities table...

[fenway]

but there are...  every record in the scenes table HAS TO have a city_id that has a row in the cities table...

Sorry, we (myself & mysql) don't believe it.  LEFT JOIN and I'll bet you'll see NULLs.

[GregL83]

hahahha. nice. we'll sorry to say it, but... you're WRONG.

 

http://forums.mysql.com/read.php?10,361671,361922#msg-361922

 

this solved my problems.