Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ncar!noao!stsci!davids
From: davids@stsci.EDU (David Silberberg)
Newsgroups: comp.databases
Subject: How do you do this query in SQL?
Keywords: SQL, select
Message-ID: <557@cirrus.stsci.edu>
Date: 18 May 89 14:11:31 GMT
Organization: Space Telescope Science Institute, Baltimore, MD 21218
Lines: 51


	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.

	Let us assume that a driver has as many as 4 cars, each of a 
different color.  The table representation is as follows.

	DRIVER					CARS
	--------------------------		-------------------
	|  id#  |  name  |  ...  |		|  id#  |  color  |
	--------------------------		-------------------

Now, suppose I would like to select all drivers that had
both RED and BLUE cars and I would like that the driver and
each of his car colors appears once in the resulting table.
For example:

	RESULT
	-------------------------------------------
	| id# | color1 | color2 | color3 | color4 |
	-------------------------------------------
	|  1  |  BLUE  |  RED   | GREEN  |  null  |
	|  2  |  RED   |  BLUE  | null   |  null  |
	|  5  |  BLUE  |  RED   | WHITE  |  TAN   |
	   etc.

How do I form this query?  Remember that each id# can only appear
once in the result.

	Similarly, I would like to select all drivers that had
either RED or BLUE cars.  The result might look like this:

	RESULT
	-------------------------------------------
	| id# | color1 | color2 | color3 | color4 |
	-------------------------------------------
	|  1  |  BLUE  |  RED   | GREEN  |  null  |
	|  2  |  RED   |  BLUE  | null   |  null  |
	|  3  |  RED   |  null  | null   |  null  |
	|  5  |  BLUE  |  RED   | WHITE  |  TAN   |
	|  7  |  TAN   |  BLUE  | null   |  null  |
	   etc.

How do I form this query?  Remember that each id# can only appear
once in the result.


Thanks in advance.

David Silberberg