SQLTeam.com | Weblogs | Forums

How to delete records with not exist

How to delete from trades table when Part id not exist on mapping table code type from and code value from?

How to delete from table trades where

Part id not exist on mapping table on code type from and code value from

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 code type from and code value from

so as example partid 1390 not exist on table mapping because code type from 8080 and code value from 13456 not exist on table mapping and so i need to delete it
so How to write query make delete from table trades where

Part id not exist on mapping code type from and code value from ?

same sample also part id 2200

have two rows
one row for map from as code type from 3030 and code value from
74000 and not exist
but code type to 7075 and code value to 14000
so i need to delete part that not have map from

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,7070,13000,0),
   (1800,8080,13570,0),
   (1800,7075,14000,0),
   (2200,3030,74000,0),
   (2200,7075,14000,0),
   (2950,3030,74000,0),
   (2950,7075,14000,0)
         
            
    create table #mapping
    (
    MapId int,
    CodeTypeFrom int,
    CodeTypeTo int,
    CodeValueFrom int,
    CodeValueTo int
    )
    insert into #mapping(MapId,CodeTypeFrom,CodeValueFrom,CodeTypeTo,CodeValueTo)
    values
    (3090,9090,13456,7070,13000),
    (3091,9095,13570,7075,14000)

expected resulr for rows must deleted

TradeCodesId PartId CodeTypeId Code PartLevel
5 1390 8080 13456 0
6 1390 7070 13000 0
7 1800 8080 13570 0
8 1800 7075 14000 0
9 2200 3030 74000 0
10 2200 7075 14000 0
11 2950 3030 74000 0
12 2950 7075 14000 0

what have you tried?