Populating Blanks on Left Join

1

Good Afternoon Team,

Hope all is well. I am hoping you can help me;

I have two tables "BC" and "A".
Table A currently contains 15 rows of data
Table BC currently contains 23 rows of data

Within the table there is a common mapping between both tables

BC.Account_code__C = a.ACCOUNT_NUMBER
BC.ISIN__C = a.SECURITY_ID

Currently i left joined these two tables based on two conditions (left join a on bc.Account_code__C = a.ACCOUNT_NUMBER and bc.ISIN__C = a.SECURITY_ID). This join returns 23 Rows ( the expected amount)

The problem i am encountering is that for 8 rows where there i was unable to match on join, the AccountType = Blank.

What i would like is that for these 8 records, to be populated with the same AccountType, as what found in table A

SELECT
bc.Account_code__C as Account_code__C,
a.ACCOUNT_TYPE
from bc
left join a on bc.Account_code__C = a.ACCOUNT_NUMBER
and bc.ISIN__C = a.SECURITY_ID
where bc.Account_code__C ='1234'

2

so bc also has a column named AccountType? hopefully that AccountType column in b, is NOT NULL

use sqlteam
go

create table #bc(Account_code__C varchar(50), ISIN__C int, 
ACCOUNT_TYPE varchar(50))
insert into #bc
select distinct top 12 name, object_id, 'KAMBOOM' from sys.objects 
union
select '1234', 99999, 'KAMBOOM'

create table #a(ACCOUNT_TYPE varchar(50), SECURITY_ID int, 
ACCOUNT_NUMBER varchar(50))
insert into #a(SECURITY_ID, ACCOUNT_NUMBER)
select ISIN__C, Account_code__C from #bc

SELECT bc.Account_code__C as Account_code__C, 
isnull(a.ACCOUNT_TYPE, bc.ACCOUNT_TYPE)  --> this is what you might need
  from #bc bc
  left join #a a on bc.Account_code__C = a.ACCOUNT_NUMBER
   and bc.ISIN__C = a.SECURITY_ID
 where bc.Account_code__C ='1234'

drop table #bc
drop table #a
3

But those 8 rows don't have a match in table A. How do you determine which AccountType to pull from table A?