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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
