Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!cornell!rochester!rutgers!att!codas!ablnc!fang!jhc
From: jhc@fang.ATT.COM (John H. Carter)
Newsgroups: comp.databases
Subject: Re: How do you do this query in SQL?
Summary: Solution to SQL problem
Keywords: SQL, select
Message-ID: <315@fang.ATT.COM>
Date: 22 May 89 17:00:18 GMT
References: <557@cirrus.stsci.edu>
Organization: AT&T, Maitland, FL
Lines: 36

In article <557@cirrus.stsci.edu>, davids@stsci.EDU (David Silberberg) writes:
> 
> 	I have a problem that I need to specify as a query in SQL.
> It has been eluding me for days, so I'll post it to the net to 
> see if anyone has an answer.
> 
> 
.
[stuff deleted]
.
> 
> Thanks in advance.
> 
> David Silberberg

Assuming you have a fixed number of color possibilities, you can 
accomplish this with a self-join as follows:


select driver.id, c1.color, c2.color, c3.color, c4.color
from driver, color c1, color c2, color c3, color c4
where
    driver.id = c1.id and c1.color = 'RED' and
    driver.id = c2.id and c2.color = 'BLUE' and
    driver.id = c3.id (+) and c3.color (+) = 'GREEN' and
    driver.id = c4.id (+) and c4.color (+) = 'TAN'

The (+) is ORACLE's notation for 'outer' join, which will give you nulls
if there are no matching rows.  INFORMIX has a different syntax and I 
assume other vendors supply similar functionality.

-- 

John Carter  -  Orlando, Fl.            |     Must have been a barge
UUCP  att!codas!fang!jhc                |         coming through!
VOICE (407) 660-3377                    |       -- Calvin & Hobbes