Multiple join + alias question

[smartalco]

I'm trying to simplify my SQL statement just for readability sake. Here's what I have for the FROM part.

FROM departments as d INNER JOIN people as p1 INNER JOIN people as p2 INNER JOIN people as p3

Instead, I would like to do something like this (except actually functional, hopefully my made up syntax is self explanatory, I'm also going to need a p4 in a different part of my script)

FROM departments as d INNER JOIN people as p1 AND p2 AND p3

The reason for having 3 different aliases for the 'people' table is there are 3 different columns in the departments table that contain a person ID corresponding to different positions. If there is a better way to do this, I'd be ok with that too.I'd also like to know how I would use LEFT JOIN instead of inner in that statement above, as I would like to return every row from 'departments' regardless of it finding a corresponding person. (Everything I've tried has thrown an error, so I've just left them all as INNER, which is currently omitting 3 rows from the 'departments' table, as apparently they have no corresponding person).Full SQL statement:

SELECT d.`id`, d.`dept`, p1.`name`, p1.`email`, p2.`name` as 'b_name', p3.`name` as 'd_name'FROM departments as d INNER JOIN people as p1 INNER JOIN people as p2 INNER JOIN people as p3WHERE d.`tech_liason_id` = p1.`id` AND d.`backup_tl_id` = p2.`id` AND d.`dept_head_id` = p3.`id`

[justsomeguy]

The first way is the right way to do it. If you want to use a left join you can just replace the inner joins with left joins. You will want to move your join conditions out of the WHERE clause though, specify the join conditions when you do each join using ON.

[smartalco]

That worked perfectly.