[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Question about joins, left outer and others
From:       Jeff Ross <jross () wykids ! org>
Date:       2010-05-07 19:38:22
Message-ID: 4BE46C46.1000100 () wykids ! org
[Download RAW message or body]

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





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]