I am trying to create a view which consists of a join on several tables.
I am wondering if it is possible to allow a column in the view to simply
take a null value if the join conditions which populate that column
fail. For example:
TABLE: UNIVERSITY (id, name)
(1, 'Stanford')
(2, 'MIT')
(3, 'UC Berkeley')
TABLE: PERSON (id, name)
(1, 'Mary Smith')
(2, 'John Doe')
(3, 'Susan Miller')
TABLE: EDUCATION (person_id, university_id, type, discipline, year)
(1, 2, 'BS', 'Computer Science', '1988')
(1, 2, 'MS', 'Electrical Engineering', '1990')
(2, 1, 'BS', 'Physics', '1985')
(2, 3, 'PhD', 'Physics', '1989')
Here's the view I'd like (I probably don't have this syntax exactly
right, but you get the idea):
CREATE VIEW PERSON_EDUCATION
(name, undergraduate, graduate, doctoral) as
select
p.name,
uu.name + eu.type + eu.discipline + eu.year,
ug.name + eg.type + eg.discipline + eg.year,
ud.name + ed.type + ed.discipline + ed.year
from
PERSON p,
EDUCATION eu,
EDUCATION eg,
EDUCATION ed,
UNIVERSITY uu,
UNIVERSITY ug,
UNIVERSITY ud
where
p.id = eu.person_id and
p.id = eg.person_id and
p.id = ed.person_id and
uu.id = eu.university_id and
ug.id = eg.university_id and
ud.id = ed.university_id