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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy