> I'm not clear how to move that "person_role.person = person.id" into
the FROM statement. Does it matter?
This should work:
FROM person
INNER JOIN person_role ON person.id = person_role.person
LEFT OUTER JOIN instructors ON (person.id = instructors.person)
LEFT OUTER JOIN class ON (instructors.class = class.id), person_role
GROUP BY person.id, last_name, person_role.role;
The reason *I* think it matters is that I like to keep my "constant"
join clauses in the from, and my variable criteria in the where. i.e.
in your query, you always want the role that joins to the person...there
are no cases (in this query) where you want other roles, therefore I
wouldn't put it in the where. I leave my where clause free for criteria
such as "where person.id = $variable" etc. I will occasionally put
variables higher in my joins, but only if EXPLAIN ANALYZE tells me it's
faster (which rarely happens for me), but I NEVER wait until my where to
clarify the fundamental criteria whereby a table in the FROM is joined.
It's just my preference for the sake of clarity.
In other words I would:
SELECT person.last_name, person_role.role FROM
person_role INNER JOIN person ON person_role.person = person.id
WHERE person.id = $variable
rather than:
SELECT person.last_name, person_role.role
FROM
person_role, person
WHERE
person_role.person = person.id AND
person.id = $variable
