Select record that have part level 0 and not have map from with the table in SQL Server

ahmedsa
 
on Mar 26, 2021 11:39 PM
875 Views

How to get Part Id that have part level 0 and not have map from ?

I work on SQL server 2012 I face issue I can't get Parts that have map to and not have map from for part level 0

So Firstly I get parts that have part level 0 then secondly

if part have code type to 1273200 then it must have code type from 974451

if part have code type to 194480 then it must have code type from 7320911

So I need to display parts that have code type 1273200 for part level 0 and not have map from 974451

OR

parts that have code type 194480 for part level 0 and not have map from 7320911

 create table #codes
 (
     PartId int,
     CodeTypeId  int,
     Partlevel int
 )
 insert into #codes(PartId,CodeTypeId,Partlevel)
 values
 ---this is correct----
 (1250,974451,0),  ---map from
 (1250,1273200,0), ---map to
 (1250,7320911,0), ---map from
 (1250,194480,0),  --map to
 ------------------
 --where map from 974451 for part id 1900 for partlevel 0
 (1900,1273200,0),---map to
 (1900,7320911,0),---map from
 (1900,194480,0),--map to
 ------------------
 (2200,974451,0),---map from
 (2200,1273200,0),---map to
 --where map from 7320911 for part id 2200 for partlevel 0
 (2200,194480,0),--map to
 -----------------
 (3400,974451,1),  --where map from 974451 for part id 3400 for partlevel 0 so if 1 it is wrong
 (3400,1273200,0), ---map to
 (3400,7320911,0), ---map from
 (3400,194480,0),  --map to
 ------------------
  --where map from 974451 for part id 3900 for partlevel 0 so if 1 then it is not exist 
 (3900,1273200,0), ---map to
 (3900,1997801,0), 
 (3900,7320911,0), ---map from
 (3900,194480,0),  --map to
    
    
 (5020,974451,1), 
 (5020,1997801,1),
 (5020,7320911,1), --where map from 7320911 for part id 5020 for partlevel 0 if 1 then it is not exist
 (5020,194480,0),  --map to
   
    
  ---map from 974451 not exist for part id 7050 but not care because I need only parts have partlevel 0
 (7050,1273200,1), ---map to
 (7050,7320911,1), ---map from
 (7050,194480,1),  --map to

  ---map from 7320911 not exist for part id 8900 for partlevel 0 if part level 1 then not exist 
 (8900,7320911,1), ---map from
 (8900,194480,0),  --map to
    
  ---map from 7320911 not exist for part id 9200 for partlevel 0  
 (9200,194480,0)  --map to
 -----------------

 Expected Result

PartId CodeTypeId Partlevel
  1900 1273200      0
  2200 194480       0
  3400 1273200      0
  3900 1273200      0
  5020 194480       0
  8900 194480       0
  9200 194480       0
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 31, 2021 08:01 AM

Check with the query.

SQL

DECLARE @codetypeid1 INT,@codetypeid2 INT,@codetypeid3 INT,@codetypeid4 INT
SET @codetypeid1 = 1273200
SET @codetypeid2 = 974451
SET @codetypeid3 = 194480
SET @codetypeid4 = 7320911

select partid, codetypeid 
from #codes main 
where partlevel = 0 and codetypeid = @codetypeid1
and not exists
(
    select 1 
    from #codes 
    where partlevel = 0 and codetypeid = @codetypeid2 and partid = main.partid
)
union all 
select partid, codetypeid 
from #codes main 
where partlevel = 0 and codetypeid = @codetypeid3 
and not exists
(
    select 1 
    from #codes 
    where partlevel = 0 and codetypeid = @codetypeid4 and partid = main.partid
)
order by partid