How to delete from trades table when Part id not exist on mapping table and have count =1 and on code type 3030?

Question

How to delete from table trades where

Part id not exist on mapping table and have count =1 and on code type 3030?

I work on sql server 2012 I face issue i can't delete partid from table trades table where Partid not exist on mapping table and have count =1 and on code type = 3030

so as example partid 2200 not exist on table mapping because code type 8080 and code type 3030 not exist on table mapping and on same time it have count 1 and his code type 3030

so How to write query make delete from table trades where

Part id not exist on mapping table and have count =1 and on code type 3030?

create table #trades  
  (  
  TradeCodesId int identity(1,1),  
  PartId int,  
  CodeTypeId int,  
  Code int,  
  PartLevel int  
  )  
  insert into #trades(PartId,CodeTypeId,Code,PartLevel)  
  values  
  (1348,9090,13456,0),  
  (1348,7070,13000,0),  
  (1387,9090,13456,0),  
  (1387,7070,13000,0),  
  (1390,8080,13456,0),  
  (1390,3030,19000,0),  
  (1800,8080,13570,0),  
  (1800,3030,28000,0),  
  (2200,3030,74000,0),  
  (2500,3030,13570,0),  
  (2950,3030,74000,0),  
  (2580,3030,13570,0)  
       
          
   create table #mapping  
   (  
   MapId int,  
   CodeTypeFrom int,  
   CodeTypeTo int,  
   CodeValueFrom int,  
   CodeValueTo int  
   )  
   insert into #mapping(MapId,CodeTypeFrom,CodeValueFrom,CodeTypeTo,CodeValueTo)  
   values  
   (3030,9090,13456,7070,13000),  
   (3035,9095,13570,7075,14000)  

expected result

TradeCodesId	PartId	CodeTypeId	Code	PartLevel  
9	2200	3030	74000	0  
10	2500	3030	13570	0  
11	2950	3030	74000	0  
12	2580	3030	13570	0  

Answer 1

Hi @ahmed salah ,

Could you please provide any update or more details?

As suggested by Erland, you could refer below method using Between instead of UNION and check whether it is working.

select *   
from #trades  
where partid in (  
select partid from #trades group by partid having count(*)=1)   
and PartId not in   
( select PartId from #trades a ,#mapping b   
where a.CodeTypeId  between iif(CodeTypeFrom>CodeTypeTo,CodeTypeFrom,CodeTypeTo) and iif(CodeTypeFrom>CodeTypeTo,CodeTypeTo,CodeTypeFrom))  
and CodeTypeId=3030  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @ahmed salah ,

Could you please provide more details about "Part id not exist on mapping table"?

After checking, all partids are not exists in mapping table.

You could refer below according to "have count =1 and on code type 3030".

 delete  
--select *   
from #trades  
where partid in (  
select partid from #trades  
group by partid  
having count(*)=1)   
and CodeTypeId=3030  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @ahmed salah ,

Please refer below updated one and check whether it is working.

--delete  
select *   
from #trades  
where partid in (  
select partid from #trades  
group by partid  
having count(*)=1)   
and CodeTypeId not in   
(select CodeTypeFrom from #mapping  
union  
select CodeTypeTo from #mapping)  
and CodeTypeId=3030  

Output:

TradeCodesId	PartId	CodeTypeId	Code	PartLevel  
9	2200	3030	74000	0  
10	2500	3030	13570	0  
11	2950	3030	74000	0  
12	2580	3030	13570	0  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Try this:

;WITH CTE_Count_Part AS (
 SELECT PartId, COUNT(*) AS PartCount
 FROM #trades
 GROUP BY PartId
),
CTE_List_Part_In_Mapping AS (
 SELECT t.PartId
 FROM #trades AS t
 INNER JOIN #mapping AS m1 ON m1.CodeTypeFrom = t.CodeTypeId AND m1.CodeValueFrom = t.Code
 INNER JOIN #mapping AS m2 ON m2.CodeTypeTo = t.CodeTypeId AND m2.CodeValueTo = t.Code
)

SELECT *
FROM #trades
WHERE PartId IN (SELECT PartId FROM CTE_Count_Part WHERE PartCount = 1)
AND  PartId NOT IN (SELECT PartId FROM CTE_List_Part_In_Mapping)
AND CodeTypeId = 3030;

Answer 5

Hi @ahmed salah ,

Please also refer below:

--delete  
select *   
from #trades  
where partid in (  
select partid from #trades group by partid having count(*)=1)   
and PartId not in   
(select PartId from #trades a inner join #mapping b on a.CodeTypeId=b.CodeTypeFrom  
union   
select PartId from #trades a inner join #mapping b on a.CodeTypeId=b.CodeTypeTo)  
and CodeTypeId=3030  

If above is still not working, please provide more sample data and expected output so that we could proceed to enhance this query. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.