basic sentence... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

basic sentence...

Author

Topic

andres1970
Starting Member

3 Posts

Posted - 2012-06-05 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-05 : 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

227 Posts

Posted - 2012-06-05 : 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 - 2012-06-05 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-05 : 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

227 Posts

Posted - 2012-06-05 : 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 - 2012-06-05 : 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

354 Posts

Posted - 2012-06-05 : 19:09:13

[code]

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

[/code]

Vijay is here to learn something from you guys.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-06 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources