Re: Can I do a conditional join?

From: Jim Wolfe <wolfj_at_snoopy.sra.com>
Date: 21 Jun 1994 08:46:49 -0400
Message-ID: <2u6nfp$dh7_at_snoopy.sra.com>

Michael Sallwasser (msallwas_at_world.nad.northrop.com) wrote:
: In article <2tkk94$e1o_at_k2.San-Jose.ate.slb.com> jgg_at_waldo.corte-madera.geoquest.slb.com (John Gillespie) writes:
: >In article <2ti0jr$e1n_at_cherokee.advtech.uswest.com>, diane_at_advtech.uswest.com (Diane Diesh) writes:
: >|> 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: (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

I'm assuming you want the undergraduate, graduate, and doctorate data for a given student on a single line. If this were not the case, i.e., you could use a single instance each of EDUCATION and UNIVERSITY. Having said that, your problem seems to indicate an outer join:

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
   eu.university_id = uu.id (+) and
   eg.university_id = ug.id (+) and
   ed.university_id = ud.id (+) and
   eu.type = 'UNDERGRADUATE' and

   eg.type = 'GRADUATE' and
   ed.type = 'DOCTORATE';

Note that your 'driver' relation is person. The EDUCATION relation is an associative entity connecting PERSON and UNIVERSITY. I assume that a row in EDUCATION must have a corresponding row in PERSON and UNIVERSITY. This would seem to indicate that the outer joins from education to university were not needed. However, since there is an outer join from person to education, this must be propogated in the join from education to university. There are also transformation techniques to get rid of the outer join, but this should get you started.
Jim Wolfe wolfj_at_snoopy.sra.com Received on Tue Jun 21 1994 - 14:46:49 CEST