Can I do a conditional join?

From: Diane Diesh <diane_at_advtech.uswest.com>
Date: 13 Jun 1994 16:13:47 GMT
Message-ID: <2ti0jr$e1n_at_cherokee.advtech.uswest.com>


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

The problem is, this view is populated only for persons who have undergraduate, graduate, and doctoral degrees. I would like to describe the view such that those columns would be null if there is no corresponding row in the EDUCATION table. Can it be done?

E-mail preferred, thanks in advance...

-- 
Diane Diesh
diane_at_advtech.uswest.com
Received on Mon Jun 13 1994 - 18:13:47 CEST

Original text of this message