How to delete from trades table when Part id not exist on mapping tabl

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

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
1. select *
2. from #trades
3. where partid in (
4. select partid from #trades group by partid having count(*)=1)
5. and PartId not in
6. ( select PartId from #trades a ,#mapping b
7. where a.CodeTypeId between iif(CodeTypeFrom>CodeTypeTo,CodeTypeFrom,CodeTypeTo) and iif(CodeTypeFrom>CodeTypeTo,CodeTypeTo,CodeTypeFrom))
8. and CodeTypeId=3030