Re: Join question - Mailing list pgsql-sql

<font style="{font-family: Arial,Verdana, Sans-Serif;font-size: 10pt;}"> have you tried a right Join?<br /><br /><br />
DanielHernndez.<br /> San Diego, CA.<br /> "The more you learn, the more you earn".<br /> Fax: (808) 442-0427<br /><br
/><br/> -----Original Message-----<br /><b>From: </b>"Edward W. Rouse" [erouse@comsquared.com]<br /><b>Date:
</b>08/15/200809:48 AM<br /><b>To: </b>pgsql-sql@postgresql.org<br /><b>Subject: </b>Re: [SQL] Join question<br /><br
/><divclass="Section1"><p class="MbtfsoNormal">I have 2 tables, both have a user column. I am currently using a left
joinfrom table a to table b because I need to show all users from table a even those not having an entry in table b.
Theproblem is I also have to include items from table b with that have a null user. There are some other criteria as
wellthat are simple where clause filters. So as an example:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">Table a:<p
class="MbtfsoNormal">Org|user<pclass="MbtfsoNormal">A    | emp1<p class="MbtfsoNormal">B    | emp1<p
class="MbtfsoNormal">B   | emp2<p class="MbtfsoNormal">B    | emp3<p class="MbtfsoNormal">C    | emp2<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">Table b:<p class="MbtfsoNormal">Org|user|color<p class="MbtfsoNormal">A  
|emp1|red<pclass="MbtfsoNormal">A   |emp1|blue<p class="MbtfsoNormal">A   |null|pink<p class="MbtfsoNormal">A  
|null|orange<pclass="MbtfsoNormal">B   |emp1|red<p class="MbtfsoNormal">B   |emp3|red<p class="MbtfsoNormal">B  
|null|silver<pclass="MbtfsoNormal">C   |emp2|avacado<p class="MbtfsoNormal"> <p class="MbtfsoNormal">If I:<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">select org, user, count(total)<p class="MbtfsoNormal">from a left join b<p
class="MbtfsoNormal">on(a.org = b.org and a.user = b.user)<p class="MbtfsoNormal">where a.org = ‘A’<p
class="MbtfsoNormal">groupby a.org, a.user<p class="MbtfsoNormal">order by a.org, a.user<p class="MbtfsoNormal"> <p
class="MbtfsoNormal">Iget:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">Org|user|count<p class="MbtfsoNormal">A   
|emp1|2<pclass="MbtfsoNormal">A    |emp2|0<p class="MbtfsoNormal">A    |emp3|0<p class="MbtfsoNormal"> <p
class="MbtfsoNormal">Butwhat I need is:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">A    |emp1|2<p
class="MbtfsoNormal">A   |emp2|0<p class="MbtfsoNormal">A    |emp3|0<p class="MbtfsoNormal">A    |null|2<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">Thanks,<p class="MbtfsoNormal">Edward W. Rouse</div></font>