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
