Simple SQL query question - 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

Simple SQL query question

Author

Topic

meishu
Starting Member

10 Posts

Posted - 2009-05-29 : 02:49:50

There are two tables.

Table1 has 1 field "code" which can get values from 1 to 10, or no value at all (NULL?).

Table2 has 2 fields. Field1 is "code" which has listed values from 1 to 10 in order. Field2 contains a list of descriptions. As such, each code can be matched to a specific description.

e.g. "code" 1 is red
..
..

"code" 9 is blue

Lets say Table1 has 10 items. That is 10 various codes.

I want to link these table1.code to table2.code and show the corresponding table2.description per each table1.code.

e.g.

Table1 fourth item code is 9. So it would check with table2.code for value 9, and the corresponding description should come up with "blue".

Any help would greatly appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 02:55:13

[code]
select t1.code, t2.description
from table1 t1 inner join table2 t2
on t1.code = t2.code
[/code]

KH
[spoiler]Time is always against us[/spoiler]

meishu
Starting Member

10 Posts

Posted - 2009-05-29 : 03:56:46

khtan, thank you for the prompt reply.

t1 other than t1.code has also t1.name.

I want to show t1.name even if t1.code is empty.

with this inner join, because t1.code is empty it will not show t1.name, right?

That is, regardless, t1.name always have to show, and if there is t1.code its coresponding t2.description should show. if there is no t1.code than the desc. would be empty.

How to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 04:03:40

then use LEFT JOIN


select t1.code, t1.name, t2.description
from   table1 t1 left join table2 t2
       on   t1.code = t2.code

KH
[spoiler]Time is always against us[/spoiler]

meishu
Starting Member

10 Posts

Posted - 2009-05-29 : 04:15:38

I understand.

What if t1 has t1.code1, t1.code2 ... t1.code10 and each of these has to be joined on t2.code, how would you do it efficiently instead of ten times?

meishu
Starting Member

10 Posts

Posted - 2009-06-03 : 01:48:14

Sorry, any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-03 : 01:59:32

quote:
Originally posted by meishu

I understand.

What if t1 has t1.code1, t1.code2 ... t1.code10 and each of these has to be joined on t2.code, how would you do it efficiently instead of ten times?

can you provide some sample data and how do you want the result to looks like

KH
[spoiler]Time is always against us[/spoiler]

meishu
Starting Member

10 Posts

Posted - 2009-06-03 : 02:24:01

Table1:
________

FlowerNumber (values 1000 to 9999)
ColorCode1 (A value can be 000 to 003)
ColorCode2 (A value can be 000 to 003)
ColorCode3 (A value can be 000 to 003)
ColorCode4 (A value can be 000 to 003)

Table2:
________
ColorCode (has a list of values from 1 to 3)
ColorName (has a list of values: red, blue, green)

Example:

Table1 item:
________
FlowerNumber: 8765
ColorCode1: 001
ColorCode2: 003
ColorCode3: 001
ColorCode4: 000

Result should show:
8765 red green red

if there are ten flowers than it should show the information for ten flowers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-03 : 02:38:05

[code]
DECLARE @Table1 TABLE
(
FlowerNumber int,
ColorCode1 int,
ColorCode2 int,
ColorCode3 int,
ColorCode4 int
)

INSERT INTO @Table1
SELECT 8765, 1, 3, 1, 0

DECLARE @Table2 TABLE
(
ColorCode int,
ColorName varchar(10)
)

INSERT INTO @Table2
SELECT 1, 'red' UNION ALL
SELECT 2, 'blue' UNION ALL
SELECT 3, 'green'

SELECT t1.FlowerNumber, c1.ColorName, c2.ColorName, c3.ColorName, c4.ColorName
FROM @Table1 t1
left JOIN @Table2 c1 ON t1.ColorCode1 = c1.ColorCode
left JOIN @Table2 c2 ON t1.ColorCode2 = c2.ColorCode
left JOIN @Table2 c3 ON t1.ColorCode3 = c3.ColorCode
left JOIN @Table2 c4 ON t1.ColorCode4 = c4.ColorCode

SELECT t1.FlowerNumber,
ColorName1 = MAX(CASE WHEN c1.ColorCode = t1.ColorCode1 THEN c1.ColorName END),
ColorName2 = MAX(CASE WHEN c1.ColorCode = t1.ColorCode2 THEN c1.ColorName END),
ColorName3 = MAX(CASE WHEN c1.ColorCode = t1.ColorCode3 THEN c1.ColorName END),
ColorName4 = MAX(CASE WHEN c1.ColorCode = t1.ColorCode4 THEN c1.ColorName END)
FROM @Table1 t1
left JOIN @Table2 c1 ON c1.ColorCode IN (t1.ColorCode1, t1.ColorCode2, t1.ColorCode3, t1.ColorCode4)
GROUP BY t1.FlowerNumber

[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources