Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA
.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill