May 24, 2002 at 3:31 am
Puzzle # 2
I have three tables :
#1. Table: Car with two columns: CarID and CarName
#2. Table: Style with two columns: StyleID and StyleName
#3. Table: CarStyle with two columns: CarID and StyleID
I ran following SQL commands:
Insert into Car Values( 1 , 'Infiniti')
Insert into Car Values( 2 , 'Acura')
Insert into Car Values( 3 , 'BMW')
Insert into Car Values( 4 , 'Lexus')
Insert into Style Values ( 1 , 'Sedan')
Insert into Style Values ( 2 , 'Coupe')
Insert into Style Values ( 3 , 'Both')
Insert into CarStyle Values ( 1 , 0) -- Check This
Insert into CarStyle Values ( 2 , 1)
Insert into CarStyle Values ( 2 , 2)
Insert into CarStyle Values ( 3 , 2)
Insert into CarStyle Values ( 4 , 1)
I want to get following output:
CarName StyleName
------- ----------
Infiniti Sedan -- Check This
Infiniti Coupe -- Check This
Acura Sedan
Acura Coupe
BMW Coupe
Lexus Sedan
What will be the SQL query to get this output?
May 24, 2002 at 4:20 am
Insert into Car Values( 1 , 'Infiniti')
Insert into Car Values( 2 , 'Acura')
Insert into Car Values( 3 , 'BMW')
Insert into Car Values( 4 , 'Lexus')
Insert into Style Values ( 1 , 'Sedan')
Insert into Style Values ( 2 , 'Coupe')
Insert into Style Values ( 3 , 'Both')
Change the CarStyle insert:-
-- Insert into CarStyle Values ( 1 , 0)
-- Check This
-- Insert into CarStyle Values ( 2 , 1)
-- Insert into CarStyle Values ( 2 , 2)
-- Insert into CarStyle Values ( 3 , 2)
-- Insert into CarStyle Values ( 4 , 1)
Insert into CarStyle Values ( 1 , 1)
Insert into CarStyle Values ( 1 , 2)
Insert into CarStyle Values ( 2 , 1)
Insert into CarStyle Values ( 2 , 2)
Insert into CarStyle Values ( 3 , 2)
Insert into CarStyle Values ( 4 , 1)
Select statement:-
select c.CarName, s.StyleName
from
Car c
inner join CarStyle cs on c.CarID = cs.CarID
inner join Style s on cs.StyleID = s.StyleID
Regards,
Andy Jones
.
June 3, 2002 at 9:56 am
Split it into two select statements with Union to combine. First select for specific StyleIDs. Second select for the all encompassing styled ID (0 - though perhaps it should be 3 given your list of styles).
select c.CarName, s.StyleName
from Car c
inner join CarStyle cs on c.CarID = cs.CarID
inner join Style s on cs.StyleID = s.StyleID
where cs.StyleID > 0
union
select c.CarName, s.StyleName
from Car c
inner join CarStyle cs on c.CarID = cs.CarID
cross join Style s
where cs.StyleID = 0
Using your data, I know this doesnt exactly give you the output you want - it gives extra line of 'Infiniti', 'Both' - though I am sure you can adjust it to your requirements.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply