Re: What kind of JOIN, if any?

Hi,

I'd look into outer joins

http://www.postgresql.org/docs/8.1/static/tutorial-join.html

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

If you want all fields from one table and only those matching from another
use outer join

HTH

Regards,

Serge Fonville

On Thu, Sep 17, 2009 at 4:29 PM, Paul M Foster <paulf(at)quillandmouse(dot)com>wrote:

> Folks:
>
> 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.
>
> Again, any help would be appreciated.
>
> Paul
>
> --
> Paul M. Foster
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>