Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!ukma!xanth!mcnc!ece-csc!ncrcae!scottj
From: scottj@ncrcae.Columbia.NCR.COM (L. Scott Johnson)
Newsgroups: comp.databases
Subject: Re: How do you do this query in SQL?
Summary: One wrong, one right
Keywords: SQL, select
Message-ID: <4512@ncrcae.Columbia.NCR.COM>
Date: 24 May 89 12:54:07 GMT
References: <557@cirrus.stsci.edu> <566@Aragorn.dde.uucp> <315@fang.ATT.COM>
Reply-To: scottj@ncrcae.Columbia.NCR.COM (L. Scott Johnson)
Organization: NCR Corp., Engineering & Manufacturing - Columbia, SC
Lines: 147

In article <566@Aragorn.dde.uucp> mj@dde.uucp (Martin Jensen) 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.
>)
>)	Let us assume that a driver has as many as 4 cars, each of a 
>)different color.  The table representation is as follows.

[ table rep. deleted]

>)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: [add:]
>where id in (select id from cars where color = 'RED'
>             intersect
>             select id from cars where color = 'BLUE')
before 'group by id'

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

>)Thanks in advance.

>)David Silberberg


>You are welcome

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

This works, and very well. I had a 29 line select that did the job, but
only if the table CARS was padded with (id, null)'s to give every id 4
entries.  Martin's version works without this qualification, and requires
less typing per query (if you don't save your commands).

Also, there was another solution posted, but it doesn't work in every case:

In article <315@fang.ATT.COM> jhc@fang.ATT.COM (John H. Carter) writes:
>In article <557@cirrus.stsci.edu>, davids@stsci.EDU (David Silberberg) writes:

.
[stuff deleted]
.

>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

This doesn't give all of the requested info.  It only lists the driver id
and the colors 'RED' and 'BLUE' , unles the other colors just happen to 
be 'GREEN' and/or 'TAN' :

SQL> select * from cars;


        ID COLOR
---------- ----------
         1 white        <-  driver 1 has red, blue and WHITE cars.
         1 red
         1 blue
         2 red
         3 tan
         3 blue

SQL> select driver.id,c1.color,c2.color,c3.color,c4.color
  2  from driver, cars c1, cars c2, cars c3, cars c4
  3  where driver.id = c1.id and c1.color = 'red' and
  4  driver.id = c2.id and c2.color = 'blue' and
  5  driver.id = c3.id (+) and c3.color (+) = 'green' and
  6* driver.id = c4.id (+) and c4.color (+) = 'tan'


        ID COLOR      COLOR      COLOR      COLOR
---------- ---------- ---------- ---------- ----------
         1 red        blue
                                 ^^^^^^^^^^

     where's white?


+------------------------------------------+----------------------------------+
|L. Scott Johnson                          |        SAUSAGE  PRINCIPLE        |
|NCR Corporation - Technical Publications  |        ------------------        |
|3325 Platt Springs Road                   |                                  |
|West Columbia, SC, 29169                  |    People who love sausage and   |
+------------------------------------------+                                  |
|                scott@tp2.Columbia.NCR.COM|   respect the law should never   |
|         ...!uunet!ncrlnk!ncrcae!tp2!scott|                                  |
|...!ucbvax!sdcsvax!ncr-sd!ncrcae!tp2!scott|    watch either one being made   |
+------------------------------------------+                                  |
| (803) 791-6435                           |                                  |
+------------------------------------------+----------------------------------+