How to copy rows based on certain conditions

Transact-SQL
1

Need help please.

I have the following tables:

create table Contacts (key1 int, Accn varchar (20))
INSERT INTO Contacts 
select 1234,'ABCD' UNION
select 1234,'EFG' UNION
select 1234,'GAB' UNION
select 1234,'PBC' UNION
select 89023,'MONQ'  
   

CREATE TABLE SCANDOCS (Accn varchar (20), FileDesc varchar (50),BReg  varchar (50))
select 'ABCD','100_MP','813' union
select 'ABCD','200_KA','843' union
select 'EFG','100_MP','3209' union
select 'GAB','800_JLZG','2357' union
select 'MONQ','700_NMO','94266' 
 

I need to find Key1 and check that its accn  in Scandocs in the column filedesc has either the values '100_MP' or '200_KA'. In the example it exists for Key1 =1234 and ACCN=ABCD,EFG. Once I find it for a specific key I need to insert a row into Scandocs for all the other accn under that key that don't have these filedesc.
Also, if an accn under a key has one of these values but with a different BReg then another acc under the same key with same fildesc I need to also insert that row into the scandocs with the accn that doesn't have that same filedesc and Breg.

The outcome should be

accn      | filedesc  | Breg
ABCD      | 100_MP    |813
ABCD      | 200_KA    |843
ABCD      | 100_MP    |3209
EFG       | 100_MP    |813
EFG       | 100_MP    |3209
EFG       | 200_KA    |843
GAB       |800_JLZG   |2357
GAB       | 100_MP    |813
GAB       | 200_KA    |843
GAB       | 100_MP    |3209
MONQ      |700_NMO    |94266

MONQ acc is under key1=89023 and no acc under that key1 has filedesc with values 100_mp or 200_KA so we don't need to copy rows with 100 or 200 since they don't exist for that key1.

Hope I was clear enough :slight_smile:

Appreciate your help.

Thanks

2

Why isn't PBC also added to your results?

;with cteBregs_100_200 as (select distinct c.Key1, FileDesc, Breg
							from #Contacts c
								join #SCANDOCS s 
									on c.accn = s.accn
							where fileDesc in ('100_MP', '200_KA'))
insert into #Scandocs 
select c.accn, b.filedesc, b.breg
 from cteBregs_100_200 b
	 join #Contacts c
		on b.key1 = c.key1
	 left join #Scandocs s 
		on c.accn = s.accn	
		and b.breg = s.breg
	where s.accn is null
3

Thanks. PBC should be added. My mistake