Multiple NULL values - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple NULL values

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2007-02-21 : 12:45:12

I'm having a problem with this query. I have the following tables:

T1--

uid----skill1----skill2----skill3
1------20--------54--------32
2------54--------32--------20
3------54--------20--------32

T2--

skillid----skillname
20---------Level1
32---------Level2
54---------Level3

I am trying to return the following result:

uid----primSkill----Secskill----Altskill
1------Level1-------Level3------Level2
2------Level3-------Level2------Level1
3------Level3-------Level1------Level2

The query I wrote is returning multiple values with NULLS. Any help is greatly appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-21 : 13:25:05

If you want us to help fix your query, it would be helpful to us if you posted the code for the query.

CODO ERGO SUM

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2007-02-21 : 13:33:10

Sure thing.

select max(a.uid) uid,
(select b.skillname where a.skill1 = b.skillid) Primskill,
(select b.skillname where a.skill2 = b.skillid) Secskill,
(select b.skillname where a.skill3 = b.skillid) Altskill
from t1 a, t2 b
where b.skill_id in (a.skill1, a.skill2, a.skill3)
group by a.uid, b.skillname, a.skill1, b.skillid, a.skill2, b.skillid, a.skill3, b.skillid

It is grouping the results but it doesn't include them all in the same row.

Thanks for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 17:47:08

[code]
select a.uid,
b.skillname as Primskill,
c.skillname as Secskill,
d.skillname as Altskill
from t1 a
inner join t2 b on a.skill1 = b.skillid
inner join t2 c on a.skill2 = c.skillid
inner join t2 d on a.skill3 = d.skillid

[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-21 : 18:41:31

if column skill1 ,skill2, or skill3 is nullable, you should use a left join.

select
	a.uid, 
	b.skillname as Primskill,
	c.skillname as Secskill,
	d.skillname as Altskill
from
	t1 a 
	left join
	t2 b
	on a.skill1 = b.skillid
	left join
	t2 c
	on a.skill2 = c.skillid
	left join
	t2 d
	on a.skill3 = d.skillid

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources