List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 24 2003 1:22pm
Subject:Re: Can I do two LEFT JOINs in one SELECT?
View as plain text  
Sorry, I've only had two sips of my morning coffee so far so I'm having 
trouble following everything you wrote. You can have a double (or more) 
condition just like in the WHERE clause. Except in this case you are 
specifying a specific type of join so you need more it from the WHERE 
section to the FROM section. Your condition dictates which tables are 
being joined.
An example:
SELECT * FROM table1, table2
LEFT JOIN table3 ON table3.field1=table1.field1 AND 
table3.fieldA=table2.fieldA

But perhaps that is not what you are trying to do. If not, then...
You first list all the table names that will be joined on what is in 
the WHERE clause. After that, only name a table after the LEFT JOIN or 
as part of a field specification. Your fields will tell MySQL what 
tables are involved in the join. Don't use commas to separate your LEFT 
JOIN statements. Think of the "LEFT JOIN" as your comma. I'm not sure 
if that's a requirement, but it helps my thinking.

So what I think you are trying to do is:
SELECT rep_fname, rep_lname, dept_name, contact_fname, contact_lname
FROM company_table,dept_table
LEFT JOIN contact_table ON contact_dept_table_id =dept_id
LEFT JOIN rep_table ON dept_rep_table_id = rep_id
WHERE dept_company_table_id = company_id
AND company_id = '20'
ORDER BY dept_name;

Drop the extra " dept_table" and commas. That should work.


On Wednesday, April 23, 2003, at 10:11 PM, Daevid Vincent wrote:

> Hello mySQL gurus, I have companies which have departments which have
> customers. Departments are owned by reps. Customers know the dept they
> belong to. Depts know the rep that owns them and the company they 
> belong to.
> Companies are dumb. That is key to understanding this. The schema is 
> below
> for the four tables. What I want to do is get a list of all reps,
> departments and contacts within those departments for a given company.
> Sometimes the rep is NULL (as in they are no longer working here) and
> sometimes the department has no contacts currently (but I still want 
> to get
> the dept info, despite it being empty).
>
> I've tried a few things with different results, but it seems I need to 
> do a
> double LEFT JOIN. Perhaps I just don't know the syntax, or perhaps this
> isn't implemented, or perhaps I just am doing this Query wrong...
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
Can I do two LEFT JOINs in one SELECT?Daevid Vincent24 Apr
  • RE: Can I do two LEFT JOINs in one SELECT?Uttam24 Apr
    • RE: INNER JOIN? Was: Can I do two LEFT JOINs in one SELECT?Daevid Vincent24 Apr
  • Re: Can I do two LEFT JOINs in one SELECT?Brent Baisley24 Apr
  • Re: Can I do two LEFT JOINs in one SELECT?Joshua J . Kugler24 Apr
RE: INNER JOIN? Was: Can I do two LEFT JOINs in one SELECT?Uttam25 Apr