However, my real reason for posting was to point out the true complexity of coding a CASE based solution with a large number of columns vs. the simplicity of coding...

Michael, if I may, you are being nit-picky at this point. 😛 Let's not assume the columns hold INT values. ...

Are you looking for something more like this?

declare @sample table (column1 int, column2 int)

declare @work table (col2 int primary key)

insert into @sample

select 123, 888 union all

select 123, 999 union all

select...

Good point about the null columns. I hadn't thought about that.

I believe this fixes it though. Not too ugly and still gets the job...

declare @sample table (column1 int, column2 int)

insert into @sample

select 123, 888 union all

select 123, 999 union all

select 123, 888 union all

select 456, 777

select *

from @sample

where column1 in (select column1...

Scott, will you clarify what you think won't work on 2005? The last thing I posted works just fine on a 2005 instance.

By the way, there are...

declare @sample table (Item varchar(20), Code varchar(20))

insert into @sample

Select 'Item1' ,'CodeA' union all

Select 'Item1' ,'CodeB' union all

Select 'Item1' ,'CodeC' union all

Select 'Item2' ,'CodeA' union all

Select 'Item2' ,'CodeB' union all

Select 'Item3'...

Use LEFT OUTER JOIN whenever you want to see rows in the primary table, whether or not matching rows exist in the joined table. INNER JOIN...

No rush, but thank you for replying.

Let me echo OPC.THREE's request.

What he is asking for are the statements to create sample tables and populate them with sample data. ...

Where did you include the index schema?

You've just stated the heart of the problem.

A seek can just mean that the query...

Solid State hard drives?

No argument, Gail. They don't have the index structure in place for a merge join.

I'm just saying that even a hash match can only run so fast.

Just looked at the last execution plan. Even though your index seeks limit the number of rows from your two tables, you are still joining almost 400,000...