Path: utzoo!utgpu!jarvis.csri.toronto.edu!rutgers!apple!sun-barr!cs.utexas.edu!uunet!mcvax!kth!sunic!dkuug!dde!mj
From: mj@dde.uucp (Martin Jensen)
Newsgroups: comp.databases
Subject: Re: How do you do this query in SQL?
Summary: Do it in Oracle - with some tricks!
Keywords: SQL, select
Message-ID: <566@Aragorn.dde.uucp>
Date: 23 May 89 09:00:06 GMT
References: <557@cirrus.stsci.edu>
Organization: Dansk Data Elektronik A/S, Herlev, Denmark
Lines: 100

)	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.
First let me do it in Oracle, it may be more difficult in other SQL systems.

The two tables are created as specified, but for reasons i will come back to
we will form an index as well:
create index car_ix on cars(id, color).

The first trict is to form a view where the cars per owner are numbered:

create view car_count as
select a.id, a.color, count(*) count
from cars a, cars b
where a.id = b.id
and a.color >= b.color  /* Note that we need the index here !! sorry */
group by a.id, a.color

Then we form the general query for all cars and owners, but so to speek
turn the colors 90 degrees:

select id, max(decode(count,1,color,null)) first,
           max(decode(count,2,color,null)) second,
           max(decode(count,3,color,null)) third,
           max(decode(count,4,color,null)) forth
from car_count
group by id

If you only  want the owners with red and blue cars:

select id, max(decode(count,1,color,null)) first,
           max(decode(count,2,color,null)) second,
           max(decode(count,3,color,null)) third,
           max(decode(count,4,color,null)) forth
from car_count
where id in (select id from cars where color = 'RED'
             intersect
             select id from cars where color = 'BLUE')
group by id
)
)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.
)
)
select id, max(decode(count,1,color,null)) first,
           max(decode(count,2,color,null)) second,
           max(decode(count,3,color,null)) third,
           max(decode(count,4,color,null)) forth
from car_count
where id in (select id from cars where
               color in ('RED', 'BLUE'))
group by id
)Thanks in advance.
)
)David Silberberg
)
)
You are welcome

Martin Jensen
E-mail:   ...!mcvax!dkuug!dde!mj    or    mj@dde.dk