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@quillandmouse.com>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.

--
Sam http://samason.me.uk/

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

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

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