How do I join 3 tables using mysql

Question

I have the following tables:

TABLE: companies c
FIELDS: id, name, description

TABLE: departments d
FIELDS: id, name, description

TABLE employees e
FIELDS: id, company_id, department_id, name

I'm trying to get a list of all employees for company 1 and also get the department name (d.name) into the results of the row.

Here's my original query:

SELECT e.*, c.name as company 
FROM employees e, companies c 
WHERE e.company_id=c.id AND e.company_id=1;

What should I add to this query to get the department name (d.name) to appear in each row of the query? Also... e.department_id may be equal to 0 in some cases since there are no departments for a specific company.

Thanks for the help everyone!

Answer 1

SELECT e.id, e.name, e.department_id, c.name, d.name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
LEFT JOIN companies AS c ON e.company_id = c.id
WHERE e.company_id = 1;

Generally you can mix-match joins in any order, as long as any columns/tables you require in a join have already been joined previously. In this case, you're slurping up data from two separate tables that are related via the employees data, and neither of them are co-dependent, so you can order the two LEFT JOIN lines in any order.

As well, with a LEFT join, you get all rows from the table on the "left" side of the join (in this case, the employees table) and matching rows (if any) from the right table (companies/departments). If there's no matching rows in either companies or departments, then any columns coming from those tables will be NULL in the result set.