Re: SQL Help: Multiple LEFT OUTER JOINs

On Mon, Nov 21, 2005 at 03:25:56PM -0600, John McCawley wrote:
> I just noticed, also goofy is your ", person_role" in your from with no
> criteria. I would generally put the "person_role.person = person.id" as
> an INNER JOIN, and then only have the "person_role.role=3" in the
> where. It doesn't look like that's the specific problem, but I
> generally find that kind of mixed syntax muddles a query.

I need to read more about the FROM clause, as I can't seem to get what
you are suggesting.

The now working query (thanks to you!) is:

SELECT person.id AS id, first_name, last_name,
count(instructors.class) as total_classes,
-- sum (CASE WHEN class.id IS NULL THEN 0 ELSE 1 END) as total_class_count,
sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count,
sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count

FROM class INNER JOIN instructors ON class.id = instructors.class
LEFT OUTER JOIN person ON person.id = instructors.person,
person_role

WHERE person_role.person = person.id
AND person_role.role = 2

GROUP BY person.id, first_name, last_name

ORDER BY future_class_count;

Not sure how to construct that. Not this, as it returns odd counts

SELECT person.id AS id,
count(instructors.class) as total_classes,
sum (CASE WHEN class.id IS NULL THEN 0 ELSE 1 END) as total_class_count, -- which is better?
sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count,
sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count

FROM class INNER JOIN instructors ON class.id = instructors.class
LEFT OUTER JOIN person ON person.id = instructors.person
INNER JOIN person_role ON person_role.person = person.id

WHERE person_role.role = 2

GROUP BY person.id, first_name, last_name

ORDER BY total_classes;

id | total_classes | total_class_count | future_class_count | past_class_count
-----+---------------+-------------------+--------------------+------------------
90 | 1 | 1 | 0 | 1
98 | 1 | 1 | 0 | 1
92 | 1 | 1 | 0 | 1
123 | 1 | 1 | 0 | 1
122 | 1 | 1 | 0 | 1
121 | 2 | 2 | 0 | 2
66 | 2 | 2 | 0 | 2
74 | 2 | 2 | 0 | 2
56 | 2 | 2 | 0 | 2
85 | 2 | 2 | 0 | 2
119 | 2 | 2 | 0 | 2
41 | 2 | 2 | 0 | 2
33 | 2 | 2 | 0 | 2
65 | 2 | 2 | 0 | 2
105 | 3 | 3 | 0 | 3
83 | 3 | 3 | 0 | 3
102 | 3 | 3 | 0 | 3
32 | 4 | 4 | 0 | 4
71 | 4 | 4 | 0 | 4
70 | 4 | 4 | 0 | 4
14 | 4 | 4 | 0 | 4
29 | 4 | 4 | 0 | 4
77 | 4 | 4 | 0 | 4
86 | 4 | 4 | 0 | 4
50 | 4 | 4 | 0 | 4
107 | 4 | 4 | 0 | 4
8 | 4 | 4 | 0 | 4
114 | 4 | 4 | 0 | 4
42 | 4 | 4 | 0 | 4
82 | 4 | 4 | 0 | 4
28 | 4 | 4 | 0 | 4
17 | 4 | 4 | 0 | 4
52 | 4 | 4 | 0 | 4
9 | 4 | 4 | 0 | 4
31 | 4 | 4 | 0 | 4

--
Bill Moseley
moseley(at)hank(dot)org