Re: SQL Help: Multiple LEFT OUTER JOINs - Mailing list pgsql-general

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@hank.org