Re: What kind of JOIN, if any?

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;

--
Mark
http://www.lambic.co.uk