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
Appreciate your help.