Re: What kind of JOIN, if any?

On Thu, Sep 17, 2009 at 11:23:12AM -0400, Mark Styles wrote:

> On Thu, Sep 17, 2009 at 10:29:11AM -0400, Paul M Foster wrote:
> > I can't find a way to do this purely with SQL. Any help would be
> > appreciated.
> >
> > Table 1: urls
> >
> > id | url
> > --------------
> > 1 | alfa
> > 2 | bravo
> > 3 | charlie
> > 4 | delta
> >
> > Table 2: access
> >
> > userid | url_id
> > ---------------
> > paulf | 1
> > paulf | 2
> > nancyf | 2
> > nancyf | 3
> >
> > The access table is related to the url table via url_id = id.
> >
> > Here's what I want as a result of a query: 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, like this:
> >
> > userid | url
> > -------------
> > paulf | alfa
> > paulf | bravo
> > | charlie
> > | delta
> >
> > I can do *part* of this with various JOINs, but the moment I specify
> > userid = 'paulf', I don't get the rows with NULLs.
>
> SELECT userid, url
> FROM urls
> LEFT OUTER JOIN (select * from access where userid = 'paulf') AS access
> ON access.url_id = urls.id;

Another good suggestion. Thanks.

Paul

--
Paul M. Foster