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
