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 |