SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 basic sentence...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andres1970
Starting Member

3 Posts

Posted - 06/05/2012 :  07:31:10  Show Profile  Reply with Quote
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
8765 Posts

Posted - 06/05/2012 :  07:37:23  Show Profile  Visit webfred's Homepage  Reply with Quote
In this example two joins are fine.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 06/05/2012 :  07:42:33  Show Profile  Reply with Quote
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"
Go to Top of Page

andres1970
Starting Member

3 Posts

Posted - 06/05/2012 :  07:49:28  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 06/05/2012 :  08:15:55  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 06/05/2012 :  08:46:20  Show Profile  Reply with Quote
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"
Go to Top of Page

andres1970
Starting Member

3 Posts

Posted - 06/05/2012 :  10:21:18  Show Profile  Reply with Quote
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
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 06/05/2012 :  19:09:13  Show Profile  Reply with Quote




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
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 06/06/2012 :  00:10:11  Show Profile  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000