select u.userid , c.companyname from users u left join company c on (u.companyid = c.companyid) where u.status = 'ACT'
Best regards, Mikhail. ----- Original Message ----- From: "Arul" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Tuesday, July 02, 2002 5:15 PM Subject: help me with this query > Hi All > > I have two tables User and Company > > User tables has userid , companyid and status > Company table has companyid and companyname > > It's not necessary that all users should have company.There can be users > without company also and for them the companyid is null. > > so i need all the users whose status are active and if they have companyid , > i need the companyname of their company > > In Oracle i can write this as > > select u.userid , (select companyname from company where companyid = > u.companyid) > from users u > where u.status = 'ACT' > > how do i get this in mysql > > i wrote one query > > select u.userid , c.companyname from > users u , company c where > u.companyid = c.companyid AND u.status = 'ACT' > > This returned only the users who have company...how do i take the users also > who doesnt have a company > > Regards > -Arul > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php