[SQL] 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): 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%' 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: 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%' '%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 [EMAIL PROTECTED]
RE: [SQL] Help with a double left join
Thanks Alex. But it was too good to be true. Or, more likely, I did something else wrong. I am still getting this 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'. when I use this 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%' Any thoughts? Thanks in advance. (see below for explanation of my DB structure if you would like). Ari -Original Message- From: Alex Pilosov [mailto:[EMAIL PROTECTED]] 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): > > 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%' > > > 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: > > 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%' > > > > '%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 [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Still stuck on a left join
I've been stuck now on a left join for about two weeks. DB is MSaccess2K, language is ASP. I'm learning php and PgSQL now, and will be migrating everything soon. In the mean time. If anyone knows where I am going wrong, please let me know. Here is my current SQL query: SELECT T.ID, T.employee, T.task, T.description, T.hours_used, T.f_date, P.project_name, C.name FROM track AS T LEFT JOIN project AS P ON (T.project=P.project_id) LEFT JOIN clients AS C ON (T.client=C.ID) WHERE T.client LIKE '%MMColParam%' Here is the error I get:[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(T.project=P.project_id) LEFT JOIN clients AS C ON (T.client=C.ID)' My DB structure: I have 5 tables: clients,project,tasks,track,users rows are as follows: clients.ID,clients.name project.project_id,client_id,project_name tasks.TaskID,tasks.Task_Name track.ID,track.employee,track.client,track.project,track.task,track.descript ion,track.hours_used,trck.f_date users.c_username,users.c_password Anyone know where I am going wrong? Thanks, Ari ~ 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 [EMAIL PROTECTED]
[SQL] You have an error in your SQL syntax near '' at line 1
Hi, I keep getting this error "You have an error in your SQL syntax near '' at line 1" when I try to run the below query. Does anyone know what I'm missing? Maybe some of the variables need to be in single or double qoutes? I tried a bunch of things, but no change. tbl_all holds the foreign keys and some columns of non-relational data. the other tbl_somethinghere tables hold two columns, the primary key and a name. $columns_to_select ="tbl_users.username_field, tbl_client.name, tbl_project.project_name, tbl_task.task_name, tbl_all.description, tbl_all.hours, tbl_all.date_field"; $left_join ="LEFT JOIN tbl_users ON tbl_all.employee_ID = tbl_users.username_field LEFT JOIN tbl_client ON tbl_all.client_ID=tbl_client.name LEFT JOIN tbl_project ON tbl_all.project_ID=tbl_project.project_name"; $left_join2="LEFT JOIN tbl_task ON tbl_all.task_ID=tbl_task.task_name"; $where="WHERE tbl_all.client_ID = $clients"; $all_query ="SELECT $columns_to_select FROM tbl_all $left_join $left_join2 $where"; Thanks, Ari ~ 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
