Обсуждение: Help with a double left join
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. -----------------------------------------------------------------------
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 > >
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 > >
"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