Join tables on CASE 1

[bmacbmac]

Hi. I have two tables:

create table #mytable1 (lot varchar(3), addition varchar(25))
insert into #mytable1 values ('9', 'ANDERSON ADD')

create table #mytable2 (lot varchar(3), addition varchar(25))
insert into mytable values ('1', 'ANDERSON ADD')
insert into mytable2 values ('9', 'ANDERSON ADD')
insert into mytable2 values ('ALL', 'ANDERSON ADD')
insert into mytable2 values ('1', 'JUNIPER SUB')
insert into mytable2 values ('9', 'ANDERSON ADD')
insert into mytable2 values ('8', 'ANDERSON ADD')

I would like to select everything in #mytable1 and join any matching addition and lot items in #mytable2:

select * 
from #mytable1
join #mytable2 on 
#mytable1.lot = #mytable2.lot
and #mytable1.addition = #mytable2.addition

However, any time the lot field shows ALL in #mytable2 I would like it to also join on #mytable1.lot = #mytable2.lot. (They use the word ALL to mean all lots). Ideally I would like:

#mytable1.lot, #mytable1.addition, #mytable2.lot, #mytable2.addition
9, ANDERSON ADD, 9, ANDERSON ADD
9, ANDERSON ADD, ALL, ANDERSON ADD

I have been playing around with CASE, but can't quite make it work... or know if this is the best way to go.

 

[Olaf Doschke]

select * 
from #mytable1
join #mytable2 on 
#mytable2.lot in (#mytable1.lot,'ALL')
and #mytable1.addition = #mytable2.addition

Bye, Olaf.

 

[bmacbmac]

Perfect thanks!