Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 signature.asc Description: Digital signature
Re: [GENERAL] What kind of JOIN, if any?
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. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] What kind of JOIN, if any?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general