On Sun, 1999-09-26 18:23:57 +0100, Michael Josephson wrote:
> However, I also need to add another condition on the rows selected
> from t2 because that table contains information for different users
> and I only want it to consider records for the current user.
> If I do:
> mysql> select t1.id, t2.info from t1 left join t2 on t1.id = t2.id
> where t2.user_name = 'mike';
> Then it matches the records for user mike correctly but no longer
> includes the ids for records where there isn't an entry in t2,
> presumably because the user_name for these isn't mike and so the
> WHERE clause fails.
Just move the additional condition up into the ON clause, so the rules
of LEFT JOIN apply to it, too:
SELECT t1.id, t2.info
FROM t1 LEFT JOIN t2
ON t1.id = t2.id
AND t2.user_name = 'mike'
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7