How to create a catchall for join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to create a catchall for join?

Author

Topic

MMMY
Starting Member

14 Posts

Posted - 2012-04-27 : 11:56:46

Example Tables:


NAME   RANK
Bill   4
John   1
Ted    2
Steve  7
Al     10


RANK   TITLE
1      General
2      Lt
3      Sarge
10     Grunt
X      Soldier

I want to check if there is a corresponding rank in the second table, and if not, then to join with X.

So an example output would be:


NAME   RANK  TITLE
Bill   4     Soldier
John   1     General
Ted    2     Lt
Steve  7     Soldier
Al     10    Grunt

Thank you so much for the help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-27 : 12:25:38

[code]
declare @name table
(
[name] varchar(10),
[rank] varchar(2)
)
insert into @name select 'Bill', '4'
insert into @name select 'John', '1'
insert into @name select 'Ted', '2'
insert into @name select 'Steve', '7'
insert into @name select 'Al', '10'

declare @title table
(
[rank] varchar(2),
[title] varchar(10)
)

insert into @title select '1', 'General'
insert into @title select '2', 'Lt'
insert into @title select '3', 'Sarge'
insert into @title select '10', 'Grunt'
insert into @title select 'X', 'Soldier'

select n.[name], [title] = coalesce(t.[title], x.[title])
from @name n
left join @title t on n.rank = t.rank
left join @title x on x.rank = 'X'

select [name], [title]
from
(
select n.[name], t.[title], rn = row_number() over (partition by n.[name] order by t.rank)
from @name n
left join @title t on n.rank = t.rank
or t.rank = 'X'
) a
where rn = 1
[/code]

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-27 : 13:15:03

If it's really just a default value you need
SELECT n.name,n.rank,ISNULL(t.title,'Soldier')
FROM @name n
LEFT JOIN @title t on n.rank = t.rank

would suffice.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:12:06

quote:
Originally posted by MMMY

Example Tables:

NAME RANK
Bill 4
John 1
Ted 2
Steve 7
Al 10

RANK TITLE
1 General
2 Lt
3 Sarge
10 Grunt
X Soldier

I want to check if there is a corresponding rank in the second table, and if not, then to join with X.

So an example output would be:

NAME RANK TITLE
Bill 4 Soldier
John 1 General
Ted 2 Lt
Steve 7 Soldier
Al 10 Grunt

Thank you so much for the help!


select	n.[name], r.[title]
from	@name n
cross apply (select top 1 TITLE
             from @title
             where RANK = n.RANK or RANK = 'X'
             order by RANK
            )r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources