Question about joins, left outer and others

Started by Jeff Rossalmost 16 years ago4 messagesgeneral
Jump to latest
#1Jeff Ross
jross@wykids.org

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

#2Andy Colson
andy@squeakycode.net
In reply to: Jeff Ross (#1)
Re: Question about joins, left outer and others

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jeff Ross (#1)
Re: Question about joins, left outer and others

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

#4Jeff Ross
jross@wykids.org
In reply to: Tom Lane (#3)
Re: Question about joins, left outer and others

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