Thread: Help with a double left join

Help with a double left join

From

"Ari Nepon"

Date:

22 June 2001, 12:26:02

I am trying to do a left join FROM [a table with two columns that have
foreign IDs] LEFT JOIN [two other tables, each has a unique ID].

I have the left join working where I join only two tables (not three):
<!--current SQL-->
SELECT track.ID, track.employee, track.client, track.task,
track.description, track.hours_used, track.f_date, project.project_name
FROM track LEFT JOIN project ON track.project=project.project_id
WHERE track.client LIKE '%MMColParam%'
<!--end-->

the two tables are track and project. Track is the left of the left join. It
holds the foreign keys. project (and later clients) are the columns with the
keys. I need to also left join clients ON track.client=client.ID.  Would
someone tell me how the SQL statement should be that allows me to do the two
left joins? Would it be:
<!--wishful thinking SQL-->
SELECT track.ID, track.employee, track.task, track.description,
track.hours_used, track.f_date, project.project_name, clients.name
FROM track LEFT JOIN project ON track.project=project.project_id AND LEFT
JOIN client ON track.client=clients.ID
WHERE track.client LIKE '%MMColParam%'
<!--end-->


'%MMColParam%' is just a variable used to hold a querystring variable in
case anyone was wondering.

Thanks,

Ari

(database is MS access. language is ASP. Whole thing will eventually be
redone in PostgreSQL and PHP, just as soon as I learn them:)
~~~~~~~~~~~~~~~~~~~~~~~~~
Ari Nepon
MRB Communications
4520 Wilde Street, Ste. 2
Philadelphia, PA 19127
p: 215.508.4920
f: 215.508.4590
http://www.mrbcomm.com

-----------------------------------------------------------------------
Sign up for our email list and receive free information about
topics of interest to nonprofit communications, marketing, and
community building professionals. Free resources, articles, tips.
Go to http://www.mrbcomm.com and use the Mailing List form.
-----------------------------------------------------------------------

RE: Help with a double left join

From

"Ari Nepon"

Date:

22 June 2001, 13:11:59

Thanks Alex. But it was too good to be true. Or, more likely, I did
something else wrong.
I am still getting this error:
<!--Start error-->
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'track.project=project.project_id LEFT JOIN client ON
track.client=clients.ID'.
<!--end error-->

when I use this SQL:
<!--start SQL-->
SELECT track.ID, track.employee, track.task, track.description,
track.hours_used, track.f_date, project.project_name, clients.name
FROM track LEFT JOIN project ON track.project=project.project_id LEFT JOIN
client ON track.client=clients.ID
WHERE track.client LIKE '%MMColParam%'
<!--end SQL-->


Any thoughts? Thanks in advance. (see below for explanation of my DB
structure if you would like).

Ari



-----Original Message-----
From: Alex Pilosov [mailto:alex@pilosoft.com]
Sent: Friday, June 22, 2001 9:42 AM
To: Ari Nepon
Cc: Pgsql-Sql
Subject: Re: [SQL] Help with a double left join


On Wed, 20 Jun 2001, Ari Nepon wrote:

> I am trying to do a left join FROM [a table with two columns that have
> foreign IDs] LEFT JOIN [two other tables, each has a unique ID].
>
> I have the left join working where I join only two tables (not three):
> <!--current SQL-->
> SELECT track.ID, track.employee, track.client, track.task,
> track.description, track.hours_used, track.f_date, project.project_name
> FROM track LEFT JOIN project ON track.project=project.project_id
> WHERE track.client LIKE '%MMColParam%'
> <!--end-->
>
> the two tables are track and project. Track is the left of the left join.
It
> holds the foreign keys. project (and later clients) are the columns with
the
> keys. I need to also left join clients ON track.client=client.ID.  Would
> someone tell me how the SQL statement should be that allows me to do the
two
> left joins? Would it be:
> <!--wishful thinking SQL-->
> SELECT track.ID, track.employee, track.task, track.description,
> track.hours_used, track.f_date, project.project_name, clients.name
> FROM track LEFT JOIN project ON track.project=project.project_id AND LEFT
remove the 'AND'

FROM track LEFT JOIN project ON track.project=project.project_id LEFT

> JOIN client ON track.client=clients.ID
> WHERE track.client LIKE '%MMColParam%'
> <!--end-->
>
>
> '%MMColParam%' is just a variable used to hold a querystring variable in
> case anyone was wondering.
>
> Thanks,
>
> Ari
>
> (database is MS access. language is ASP. Whole thing will eventually be
> redone in PostgreSQL and PHP, just as soon as I learn them:)
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> Ari Nepon
> MRB Communications
> 4520 Wilde Street, Ste. 2
> Philadelphia, PA 19127
> p: 215.508.4920
> f: 215.508.4590
> http://www.mrbcomm.com
>
> -----------------------------------------------------------------------
> Sign up for our email list and receive free information about
> topics of interest to nonprofit communications, marketing, and
> community building professionals. Free resources, articles, tips.
> Go to http://www.mrbcomm.com and use the Mailing List form.
> -----------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Re: Help with a double left join

From

Alex Pilosov

Date:

22 June 2001, 13:36:06

On Wed, 20 Jun 2001, Ari Nepon wrote:

> I am trying to do a left join FROM [a table with two columns that have
> foreign IDs] LEFT JOIN [two other tables, each has a unique ID].
> 
> I have the left join working where I join only two tables (not three):
> <!--current SQL-->
> SELECT track.ID, track.employee, track.client, track.task,
> track.description, track.hours_used, track.f_date, project.project_name
> FROM track LEFT JOIN project ON track.project=project.project_id
> WHERE track.client LIKE '%MMColParam%'
> <!--end-->
> 
> the two tables are track and project. Track is the left of the left join. It
> holds the foreign keys. project (and later clients) are the columns with the
> keys. I need to also left join clients ON track.client=client.ID.  Would
> someone tell me how the SQL statement should be that allows me to do the two
> left joins? Would it be:
> <!--wishful thinking SQL-->
> SELECT track.ID, track.employee, track.task, track.description,
> track.hours_used, track.f_date, project.project_name, clients.name
> FROM track LEFT JOIN project ON track.project=project.project_id AND LEFT
remove the 'AND'

FROM track LEFT JOIN project ON track.project=project.project_id LEFT

> JOIN client ON track.client=clients.ID
> WHERE track.client LIKE '%MMColParam%'
> <!--end-->
> 
> 
> '%MMColParam%' is just a variable used to hold a querystring variable in
> case anyone was wondering.
> 
> Thanks,
> 
> Ari
> 
> (database is MS access. language is ASP. Whole thing will eventually be
> redone in PostgreSQL and PHP, just as soon as I learn them:)
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> Ari Nepon
> MRB Communications
> 4520 Wilde Street, Ste. 2
> Philadelphia, PA 19127
> p: 215.508.4920
> f: 215.508.4590
> http://www.mrbcomm.com
> 
> -----------------------------------------------------------------------
> Sign up for our email list and receive free information about
> topics of interest to nonprofit communications, marketing, and
> community building professionals. Free resources, articles, tips.
> Go to http://www.mrbcomm.com and use the Mailing List form.
> -----------------------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
>

Re: Help with a double left join

From

Tom Lane

Date:

22 June 2001, 14:17:37

"Ari Nepon" <anepon@verveinternet.com> writes:
> Thanks Alex. But it was too good to be true. Or, more likely, I did
> something else wrong.

clients.ID => client.ID, likely.

regression=# create table track(project int, client int);
CREATE
regression=# create table project(project_id int);
CREATE
regression=# create table client(ID int);
CREATE
regression=# select * from track LEFT JOIN project ON track.project=project.pro
ject_id
regression-# LEFT JOIN client ON track.client=clients.ID;
ERROR:  Relation 'clients' does not exist
regression=# select * from track LEFT JOIN project ON track.project=project.pro
ject_id
regression-# LEFT JOIN client ON track.client=client.ID;project | client | project_id | id
---------+--------+------------+----
(0 rows)
    regards, tom lane