Обсуждение: Question about joins, left outer and others

This is going to be simple, I'm sure, but I'm stumped.  It's a little long but
I thought as I wrote it out I'd get the aha! moment and since that didn't
happen, here goes.

I have a table of education categories:

jross@wykids localhost# \d education_categories
                                   Table "public.education_categories"
       Column       |  Type   |                                Modifiers

-------------------+---------+--------------------------------------------------
  ed_cat_id         | integer | not null default
nextval('education_categories_ed_cat_id_seq'::regclass)
  ed_cat_name       | text    | not null
  ed_cat_short_name | text    | not null
Indexes:
     "education_categories_pkey" PRIMARY KEY, btree (ed_cat_id)
Referenced by:
     TABLE "people_education_categories" CONSTRAINT
"people_education_categories_pp_ed_cat_id_fkey" FOREIGN KEY (pp_ed_cat_id)
REFERENCES education_categories(ed_cat_id)

filled with these values:

jross@wykids localhost# select * from education_categories order by ed_cat_id;
  ed_cat_id |        ed_cat_name        | ed_cat_short_name
-----------+---------------------------+-------------------
          1 | Some High School          | some_high_school
          2 | High School or GED        | high_school
          3 | Some College              | some_college
          4 | Trade or Technical School | trade
          5 | Certificate or License    | cert
          6 | Associates                | associates
          7 | Bachelors                 | bachelors
          8 | Masters                   | masters
          9 | Doctorate                 | doctorate
(9 rows)

I have another table that contains information about the education level
someone might have.  A person can have more than one entry in the table--the
idea is to track their education status over time.

jross@wykids localhost# \d people_education_categories
                      Table "public.people_education_categories"
          Column          |            Type             |          Modifiers

-------------------------+-----------------------------+------------------------
  pp_ed_cat_id            | integer                     | not null
  pp_ed_cat_pp_id         | integer                     | not null
  pp_ed_cat_subject       | text                        |
  pp_ed_cat_institution   | text                        |
  pp_ed_cat_date_achieved | date                        |
  pp_ed_cat_date_expires  | date                        |
  pp_ed_cat_date_recorded | timestamp without time zone | default
('now'::text)::date
Foreign-key constraints:
     "people_education_categories_pp_ed_cat_id_fkey" FOREIGN KEY
(pp_ed_cat_id) REFERENCES education_categories(ed_cat_id)
     "people_education_categories_pp_ed_cat_pp_id_fkey" FOREIGN KEY
(pp_ed_cat_pp_id) REFERENCES people(pp_id)

I'm trying see all of the possible education categories for any person.  If
they have no entry in the people education categories table for the
corresponding category I'd want the null  values to display, so for someone
with a bachelors degree I'd like to see something like this:

        Level               |      Subject        |    Institution
---------------------------+---------------------+----------------------
  Some High School          |                     |
  High School or GED        |                     |
  Some College              |                     |
  Trade or Technical School |                     |
  Certificate or License    |                     |
  Associates                |                     |
  Bachelors                 |Elementary Education | University of Wyoming
  Masters                   |                     |
  Doctorate                 |                     |


Here's a sample entry of a person with a bachelor's degree--this is the only
entry for this person in the people_education_categories table:

jross@wykids localhost# select * from people_education_categories where
pp_ed_cat_pp_id = 1796;
-[ RECORD 1 ]-----------+--------------------------------
pp_ed_cat_id            | 7
pp_ed_cat_pp_id         | 1796
pp_ed_cat_subject       | Elementary Education/Fine Arts
pp_ed_cat_institution   |
pp_ed_cat_date_achieved |
pp_ed_cat_date_expires  |
pp_ed_cat_date_recorded | 2009-12-17 00:00:00

To get the output I want above, I'd think I'd need to do a left outer join
like this:

jross@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as
"Subject", pp_ed_cat_institution as "Institution" from
education_categories left outer join people_education_categories on
(ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;

but that only gives me this:

    Level   |             Subject            | Institution
-----------+--------------------------------+-------------
  Bachelors | Elementary Education/Fine Arts |
(1 row)

In fact, every join combination (left|right|full|inner|outer) I've tried gives
me the same result.

Clearly I'm missing something obvious and will welcome all hints,
clue-by-fours and so on.

Thanks,

Jeff Ross

On 5/7/2010 2:38 PM, Jeff Ross wrote:
> This is going to be simple, I'm sure, but I'm stumped.  It's a little
> long but I thought as I wrote it out I'd get the aha! moment and since
> that didn't happen, here goes.
>

snip

> To get the output I want above, I'd think I'd need to do a left outer
> join like this:
>
> jross@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject
> as "Subject", pp_ed_cat_institution as "Institution" from
> education_categories left outer join people_education_categories on
> (ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;
>
> but that only gives me this:
>
> Level | Subject | Institution
> -----------+--------------------------------+-------------
> Bachelors | Elementary Education/Fine Arts |
> (1 row)
>

Its the "where pp_ed_cat_pp_id = 1796" that is causing it, not the join.

try adding "or pp_ed_cat_pp_id is null"

-Andy

Jeff Ross <jross@wykids.org> writes:
> To get the output I want above, I'd think I'd need to do a left outer join
> like this:

> jross@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as
> "Subject", pp_ed_cat_institution as "Institution" from
> education_categories left outer join people_education_categories on
> (ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;

This query is actually equivalent to a plain join.  The reason is that
no null-extended row can possibly satisfy the WHERE clause, since the
WHERE is constraining a field from the inner side of the join.  Remember
WHERE filters the output of the join.

Possibly what you want is

    select ... from
    education_categories left outer join people_education_categories on
    (ed_cat_id = pp_ed_cat_id and pp_ed_cat_pp_id = 1796);

            regards, tom lane

Tom Lane wrote:
> Jeff Ross <jross@wykids.org> writes:
>> To get the output I want above, I'd think I'd need to do a left outer join
>> like this:
>
>> jross@wykids localhost# select ed_cat_name as "Level", pp_ed_cat_subject as
>> "Subject", pp_ed_cat_institution as "Institution" from
>> education_categories left outer join people_education_categories on
>> (ed_cat_id = pp_ed_cat_id) where pp_ed_cat_pp_id = 1796;
>
> This query is actually equivalent to a plain join.  The reason is that
> no null-extended row can possibly satisfy the WHERE clause, since the
> WHERE is constraining a field from the inner side of the join.  Remember
> WHERE filters the output of the join.
>
> Possibly what you want is
>
>     select ... from
>     education_categories left outer join people_education_categories on
>     (ed_cat_id = pp_ed_cat_id and pp_ed_cat_pp_id = 1796);
>
>             regards, tom lane
>

Thanks, Tom and Andy.  This is exactly what I was missing.

Jeff Ross