I'm not but his original query used JIONs and a LEFT JOIN is (IMHO) the easiest to understand. I didn't have the presence of mind to rewrite the query using a simple WHERE clause - hope your tip helps him.
btw: can anyone say if the LEFT JOIN or the alternative WHERE statement (in general?) is faster?
I can't speak to the speed, although since JOINs (can) do more than simple matching, I'd say the where condition may turn out a little better (after crossing fingers that mysql will use the proper indices in all cases).
An example of something I can't think of how to write into a where clause (easily, at least):
from http://dev.mysql.com/doc/mysql/en/JOIN.html :
"
If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;
"
The 'explain' syntax can give you some idea of this, as well as help you in optimizing indices and queries.
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
Cheers,
--
- Martin Norland, Database / Web Developer, International Outreach x3257
The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
