davids@stsci.EDU (David Silberberg) (05/18/89)
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
schi@hpcupt1.HP.COM (Shan Chi) (05/20/89)
It is strange to me that the result table contains NULL value. I don't remember that SQL select can generate values that do not exist in the source tables (except for values returned by aggregation functions). Therefore it might be necessary to do several selects, one for those who have two cars, one for those who have three cars, and so on.
jhc@fang.ATT.COM (John H. Carter) (05/23/89)
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
mj@dde.uucp (Martin Jensen) (05/23/89)
) 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.dkscottj@ncrcae.Columbia.NCR.COM (L. Scott Johnson) (05/24/89)
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 | | +------------------------------------------+----------------------------------+