Re: What kind of JOIN, if any? - Mailing list pgsql-general

On Thu, Sep 17, 2009 at 10:29:11AM -0400, Paul M Foster wrote:
> I want all the records of the
> url table, one row for each record, plus the userid field that goes with
> it, for a specified user (paulf), with NULLs as needed

Maybe something like this?

  SELECT a.userid, u.url
  FROM urls u
    LEFT JOIN access a ON u.id = a.url_id AND a.userid = 'paulf';

> I can do *part* of this with various JOINs, but the moment I specify
> userid = 'paulf', I don't get the rows with NULLs.

I guess you were putting "userid = 'paulf'" into the WHERE clause,
that's the wrong place.  It needs to be up in the ON clause.

--
  Sam  http://samason.me.uk/