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

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

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

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

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