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.

---

--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

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

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

---