Can anyone explain?

[ronphoton]

Hi - I am (new to) using MySQL 5.0 and am just learning.

I am using a book to learn and recently got stuck on this piece of code. I eventually fixed it by reversing the order of the table names in the FROM clause, but can't see why it made a difference. Can anyone please explain it to me so I can make sure I understand and learn. Thanks.

Here's the SELECT statement that did NOT work:
-------------------------------------------------------
$sql = "SELECT a.name, a.type_id, b.title,
b.description, b.content_date,
b.create_date, b.created_by,
b.last_upd_date, b.last_upd_by,
c.name as dept_name, content_id

FROM Content_Type a, Department c

LEFT OUTER JOIN Content b on
a.type_id = b.content_type
and a.type_id = b.content_type
and b.dept_id = $dept_id
and b.content_type = $type_id

WHERE c.dept_id = $dept_id

ORDER BY content_date DESC";
-------------------------------------------------------
I keep getting the error that a.type_id was not recognized.


Once I changed the FROM order to:
$sql = "SELECT a.name, a.type_id, b.title,
b.description, b.content_date,
b.create_date, b.created_by,
b.last_upd_date, b.last_upd_by,
c.name as dept_name, content_id

FROM Department c, Content_type a

LEFT OUTER JOIN Content b on
a.type_id = b.content_type
and a.type_id = b.content_type
and b.dept_id = $dept_id
and b.content_type = $type_id

WHERE c.dept_id = $dept_id

ORDER BY content_date DESC";
-------------------------------------------------------
it worked as expected!

Why did reversing the order in the FROM clause fix the problem?

Thank you
Ron

[felgall]

You are doing a left outer join and so the appropriate table needs to be the one on the left when referenced. With the table names the other way around a right outer join would give the same result.

[ronphoton]

Hi Stephen

And the "left" (or right) in the join clause is relevant to the "WHERE" clause? Is that correct?

Ron