How to rewrite SQL query for new MySQL LEFT JOINS

[Gmail User]

I hope someone can clue me in what a syntax of query that produces the
same would look like for MySQL  5.0.12

Old query meant to list most recent message from each thread, e.g.

select * from messages left join messages as messages_ on
messages.thread = messages_.thread and messages.created 
messages_.created where messages_.id is null

It worked in 4.x but does not work in the new syntax. How should I
rewrite it to get the same result?

TIA,

Ed

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to rewrite SQL query for new MySQL LEFT JOINS

[Gmail User]

It worked in 4.x but does not work in the new syntax. How should I
rewrite it to get the same result?


OK, that was a lie. It works in 5.x as well. I should learn to
describe my problem more accurately as well as RTFM :-(

The correct description of the query in question would have been:

select * from messages, someothertable  left
join messages as messages_ on
messages.thread = messages_.thread and messages.created 
messages_.created where messages_.id is null

Since there was a comma (inner) join there, the left join applied only
to 'someothertable' and not to message table as intended. As I
learned, in 5.0.12+, parentheses matter!

Duh!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]