Author |
Topic  |
|
andres1970
Starting Member
3 Posts |
Posted - 06/05/2012 : 07:31:10
|
Hi everybody!
I must to do a very basic sql sentence but I don't see how to do it. In fact I've do it using two INNER JOIN, but I'm sure it must be more easy.
Basically, I've a very basic product table with
product.name product.idcolor1 product.idcolor2
and a very basic color table
color.id color.name
What sentence must I use to obtain, for exemple,
t-shirt 1, green, black t-shrit 2, white, red
I mean, to substitute the idcolor1 and the idecolor2 for their names.
thank you very much!! Andres.
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 06/05/2012 : 07:37:23
|
In this example two joins are fine.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/05/2012 : 07:42:33
|
Hi Andres,
Welcome to SQLTeam. A join should be sufficient for your requirement.
You could get a more elaborate solution if you post some sample data.
N 28° 33' 11.93148" E 77° 14' 33.66384" |
 |
|
andres1970
Starting Member
3 Posts |
Posted - 06/05/2012 : 07:49:28
|
Hey!
Well, for example, I can have
TABLE products (product_name, color1, color2) t-shirt 1 3 short 2 2
TABLE colors (id, color_name) 1 green 2 red 3 black
And I want to obtains with the more easy sentence
t-shirt green black short red red
I've do it with 2 JOIN but maybe there're other more nice says to do.
thanks!! andres. |
 |
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 06/05/2012 : 08:15:55
|
quote: Originally posted by webfred
In this example two joins are fine.
No, you're never too old to Yak'n'Roll if you're too young to die.
You have two different IDs to match so two joins are fine 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/05/2012 : 08:46:20
|
Doing the Two Joins would be the best. But, I just came to know that you can do it with a single Join like this:
--Creating Tables
Create Table Ex
(product_name varchar(10),
color1 int,
color2 int )
Create Table Ex1
(id int,
color_name varchar(10) )
--Inserting Sample Data
Insert Into Ex
Select 't-shirt', 1, 3
Union ALL
Select 'short', 2, 2
Insert into Ex1
Select 1, 'green'
Union ALL
Select 2, 'red'
Union ALL
Select 3, 'black'
--Query
Select a.product_name,
Max(Case When a.color1 = b.id Then b.color_name Else '' End) As Color1,
Max(Case When a.color2 = b.id Then b.color_name Else '' End) As Color2 From Ex As a
JOIN Ex1 As b On (a.color1 = b.id Or a.color2 = b.id)
Group By a.product_name
Order By a.product_name Desc
N 28° 33' 11.93148" E 77° 14' 33.66384" |
 |
|
andres1970
Starting Member
3 Posts |
Posted - 06/05/2012 : 10:21:18
|
Hi vinu,
Thanks for your option! A very smart solution. But, yes I agree with you, I think that 2 INNER are a little more easy.
Thank you1 Andres |
 |
|
vijays3
Constraint Violating Yak Guru
India
354 Posts |
Posted - 06/05/2012 : 19:09:13
|
Create Table #Ex
(product_name varchar(10),
color1 int,
color2 int )
Create Table #Ex1
(id int,
color_name varchar(10) )
--Inserting Sample Data
Insert Into #Ex
Select 't-shirt', 1, 3
Union ALL
Select 'short', 2, 2
Insert into #Ex1
Select 1, 'green'
Union ALL
Select 2, 'red'
Union ALL
Select 3, 'black'
select e1.Product_name,e3.color_name,e4.color_name from #Ex e1
full outer join #Ex1 e2 on e1.color1 = e2.id
right join #Ex1 e3 on e1.color1= e3.id left join #Ex1 e4 on e1.color2= e4.id
where Product_name is not null
SELECT Product_name,
CASE WHEN color1 =1 THEN 'Green'
WHEN color1 =2 THEN 'red'
WHEN color1 =3 THEN 'Black'END AS COLOR1,
CASE WHEN color2 =1 THEN 'Green'
WHEN color2 =2 THEN 'red'
WHEN color2 =3 THEN 'Black'END AS COLOR2
FROM #EX
Drop table #Ex
Drop table #EX1
Vijay is here to learn something from you guys. |
Edited by - vijays3 on 06/05/2012 19:21:37 |
 |
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/06/2012 : 00:10:11
|
quote: Originally posted by andres1970
Hi vinu,
Thanks for your option! A very smart solution. But, yes I agree with you, I think that 2 INNER are a little more easy.
Thank you1 Andres
You can always test the Execution Plans and the Time taken for both the queries to know which one is better Performance-wise. I just posted it because I thought it was an interesting way of doing it.
N 28° 33' 11.93148" E 77° 14' 33.66384" |
 |
|
|
Topic  |
|