Hi, I have three tables and all three are linked. Please help for the query so that I can get the desired result. Thanks.
Notes for Table_A and Table_B:
-----------------------------------
ROW Data are given in the variables to insert the Row data. If these Row data are already exist with the exactly same sequence in the row of table then don't need to INSERT data again.
If these variable date doen't exist then need to add this row.
-----------------------------------
Seq_id_Table_A is a Seq_id of #table_A. Seq_id_Table_B is a Seq_id of #table_B.
--Table_A----------------------Variables for Table_A--------------------
Declare @table_A_Y char(4)='TRC'
Declare @table_A_Y1 char(2)='1'
Declare @table_A_Y2 char(1)='D'
Declare @table_A_Z char(4)='XRC'
Declare @table_A_Z1 char(2)='2'
Declare @table_A_Z2 char(1)='E'
Create table #table_A (Seq_id numeric, Y char(4),Y1 char(2), Y2 char(1), Z char(4),Z1 char(2), Z2 char(1))
insert into #table_A values (1,'TRC','2','F','XRC','3','G')
insert into #table_A values (2,'TRC','3','F','XRC','4','G')
--Table_B----------------------Variables for Table_B--------------------
Declare @table_B_N char(10)='Papering'
Declare @table_B_M char(10)='Scanning'
Create table #table_B (Seq_id numeric, N char(10),M char(10))
insert into #table_B values (1,'Papering','Scanning')
--Table_C---------------------------------------------------------------
Create table #table_C (Seq_id_Table_A numeric, Seq_id_Table_B numeric)
insert into #table_C values (2,1)
Desired Result:
Select * from #table_A
Seq_id Y Y1 Y2 Z Z1 Z2
1 TRC 2 F XRC 3 G
2 TRC 3 F XRC 4 G
3 TRC 1 D XRC 2 E ---This data was in variable and this exact row was not already exist so need to be added and store seq_id for #table_C. Tricky part is that if this row was already exist then
don't need to add this row but store seq_id for #table_c.
Select * from #table_B
Seq_id N M
1 Papering Scanning ---This data was in variable to insert the Row data and this exact row was already exist so don't need to be added but need
to store seq_id for #table_C.
Select * from #table_C
Seq_id_Table_A Seq_id_Table_B
2 1
3 1
--- Added new row. Linked seq_id of table_A and table_B