How to select the value by comparing multiple tables (MS SQL, VB .NET)

See more:

VB

SQL-Server-2012

Hello All,
I am a not a expert on MS SQL 2017 or VB .Net 2019 but trying to do a small project with my knowledge. I need a help to fetch the record from multiple table and delete them based on the conditions. Below is the table structure:

1st table (

Inward_Part

) :

select * from Inward_Part

Intenal_no|IP_InDt|IP_Desc|IP_DaNo|IP_PartName|IP_JobRate|IP_In_Qty|IP_TotVal|IP_Out_Qty|IP_Qty_Balance|IP_Progress_Status
1|2020-01-21|Desc Added|da1|Part1|50.00|50.00|2500.00|50.00|0.00|close
2|2020-01-25|Desc Added|da2|Part1|50.00|50.00|2500.00|50.00|0.00|semi-close
4|2020-01-25|Desc Added|da4|Part1|50.00|50.00|2500.00|30.00|20.00|progress
5|2020-01-25|Desc Added|da5|Part1|50.00|50.00|2500.00|50.00|0.00|close
3|2020-01-25|Desc Added|da3|Part1|50.00|50.00|2500.00|50.00|0.00|semi-close

2nd table (

Outward_Master_Info

):

select * from Outward_Master_Info

DC_Internal_no|DC_no|DC_Date|DC_PartName|DC_TotalQty|DC_TotalVal|Remarks|Status
1|20180001  |2020-01-21|Part1|40.00|2000.00||Close
2|20180002  |2020-01-21|Part1|40.00|2000.00||Close
3|20180003  |2020-01-21|Part1|50.00|2500.00||Close
4|20180004  |2020-01-21|Part1|10.00|500.00||Open
6|20180006  |2020-01-21|Part1|10.00|500.00||Open
5|20180005  |2020-01-21|Part1|30.00|1500.00||Open
7|20180007  |2020-01-21|Part1|50.00|2500.00||Close

3rd table (

Outward_Parts_info

) :

select * from Outward_Parts_info

Part_Internal_no|Part_DC_no|Part_DA_No|Part_DA_Job_Rate|Part_Qty|Part_Val|Part_Status
1|20180001  |da1|50.00|20.00|1000.00|close
2|20180001  |da2|50.00|20.00|1000.00|close
3|20180002  |da1|50.00|20.00|1000.00|close
4|20180002  |da3|50.00|20.00|1000.00|close
5|20180003  |da1|50.00|10.00|500.00|close
6|20180003  |da2|50.00|20.00|1000.00|close
7|20180003  |da4|50.00|20.00|1000.00|close
12|20180006  |da3|50.00|10.00|500.00|Open 
9|20180004  |da2|50.00|10.00|500.00|Open 
10|20180005  |da3|50.00|20.00|1000.00|Open 
11|20180005  |da4|50.00|10.00|500.00|Open 
13|20180007  |da5|50.00|50.00|2500.00|close

Now i need to delete the

IP_DaNo

from table 1 and

DC_no

from table 2 and 3 when all the DA and DC are closed and also when only the total sum of

Part_DA_No

qty in table 3 is equal to input qty in table 1 and all should be in close status

For example from above entry I need to delete da5 entries from table 3 and table 1 and its corresponding DC no (

20180007

) from table 2 alone.

if we see from the above example da1 is also closed and its out put qty matches with input qty, but

20180001

DC has two da (da1 and da2) in which da2 is not closed fully in table 3, so this record should not be deleted

What I have tried:

what i tried is the below query
1:

select * from Inward_Part IP,
		(select sum(Part_Qty) qtysum,Part_DA_No from Outward_Parts_info where Part_Status ='close' group by Part_DA_No) SMV
where ip.IP_Out_Qty = SMV.qtysum and ip.IP_Progress_Status = 'close' and ip.IP_DaNo = SMV.Part_DA_No

but the query fetch both da1 and da5 entry

Quote:

Intenal_no|IP_InDt|IP_Desc|IP_DaNo|IP_PartName|IP_JobRate|IP_In_Qty|IP_TotVal|IP_Out_Qty|IP_Qty_Balance|IP_Progress_Status|qtysum|Part_DA_No
1|2020-01-21|Desc Added|da1|Part1|50.00|50.00|2500.00|50.00|0.00|close|50.00|da1
5|2020-01-25|Desc Added|da5|Part1|50.00|50.00|2500.00|50.00|0.00|close|50.00|da5

2: this also fetch both da1 and da5 entry

select distinct IP.IP_DaNo from Inward_Part IP, Outward_Master_Info OM, Outward_Parts_info OP where IP.IP_Progress_Status = 'close' and ip.IP_DaNo = op.Part_DA_No
and op.Part_DC_no = om.DC_no and op.Part_Status = 'close' and om.Status = 'close'

IP_DaNo
da1
da5

Kindly help me with the query to fetch all das (like da5) who's status close and input qty in table 1 is equal to sum of out qty in table 3 (group by da_nos)

Thank and Regards
NS