Обсуждение: 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

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