Re: What kind of JOIN, if any?

On Thu, Sep 17, 2009 at 04:20:57PM +0100, Sam Mason wrote:

> 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.

You da man. That is the answer; it worked. Thanks very much.

(BTW, on your website, the link from "Simple Report Generator" to
http://samason.me.uk/~sam/reportgen/ is broken.)

Paul

--
Paul M. Foster