List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 27 1999 1:05am
Subject:Re: Including all rows from one of the tables in a join
View as plain text  
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'
  ;

Regards,
  Martin
-- 
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
Thread
Including all rows from one of the tables in a joinMichael Josephson26 Sep
  • Re: Including all rows from one of the tables in a joinMartin Ramsch27 Sep
  • Re: Including all rows from one of the tables in a joinAlok K. Dhir27 Sep
  • Re: Including all rows from one of the tables in a joinMichael Josephson28 Sep