Re: multi-table join, final table is outer join count ...

Got it after a bit of fiddling ... actually, not bad code ...

SELECT distinct s.gid, s.created, count(i.title) AS images
FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active),
personal_data pd, relationship_wanted rw
WHERE s.active AND s.status != 0
AND (s.gid = pd.gid AND pd.gender = 0)
AND (s.gid = rw.gid AND rw.gender = 0 )
GROUP BY s.gid,s.created
ORDER BY images desc;

The part that had confused me was the whole 'ON' part ... once I clued in
that that is essentially a WHERE, it actually made sense ...

On Sat, 12 May 2001, The Hermit Hacker wrote:

>
> Okay, not sure best way to try and describe this ... have multiple tables,
> of a form like:
>
> table a
> gid int
> data text
>
> table b
> gid int
> data text
>
> table c
> gid int
> data text
>
> table d
> gid int
> data text
>
> I want to return:
>
> a.gid,a.data,b.data,c.data,count(d.data)
>
> where
>
> a.gid = b.gid = c.gid = d.gid
>
> *but* I want count(d.data) to return zero *if* there are no records in
> table d ...
>
> essentially, gid has to exist in tables a,b,c but not d ...
>
> So, ignoring table d, i'd have:
>
> SELECT a.gid,a.data,b.data,c.data
> FROM tablea a, tableb b, tablec c
> WHERE a.gid = b.gid
> AND b.gid = c.gid;
>
> How do I add 'tabled d' to the mix?
>
> Thanks ...
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
>
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org