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

Question

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

I work with SQL Server 2012 and face the issue that I can't get Parts that have map to and not have map from for same Part where part level 0 .

• If part have code type to 1273200 then it must have code type from 974451 for part level 0 .

• If part have code type to 194480 then it must have code type from 7320911 for part level 0 .

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

Code:

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
 -----------------

what i try is

select partid,codetypeid from #codes
where partlevel=0 and codetypeid=974451 and codetypeid <> 1273200
group by partid,codetypeid

union all

select partid,codetypeid from #codes
where partlevel=0 and codetypeid=194480 and codetypeid <> 7320911
group by partid,codetypeid

correct result must be as below :

Answer 1

Solution

1. Select rows (c1) on level zero...
   c1.PartLevel = 0
2. ... and filter on to rows.
   c1.CodeTypeId in (194480, 1273200)
3. A corresponding row (c2)...
   where c2.PartId = c1.PartId and c2.PartLevel = c1.PartLevel
4. ...must be missing...
   where not exists (...)
5. ...linked on fixed from/to pairs.
   and (c1.CodeTypeId <> 1273200 or c2.CodeTypeId = 974451)
and (c1.CodeTypeId <> 194480 or c2.CodeTypeId = 7320911)

Step 5 translates to the following in pseudo code:

if (c1.CodeTypeId = 1273200)
{
  c2.CodeTypeId = 974451;
}
if (c1.CodeTypeId = 194480)
{
  c2.CodeTypeId = 7320911;
}

Combining everything:

select c1.PartId,
       c1.CodeTypeId,
       c1.PartLevel
from codes c1
where c1.PartLevel = 0
  and c1.CodeTypeId in (194480, 1273200) -- "to"
  and not exists ( select 'x'
                   from codes c2
                   where c2.PartId = c1.PartId
                     and c2.PartLevel = c1.PartLevel
                     and (c1.CodeTypeId <> 1273200 or c2.CodeTypeId =  974451)    -- "from" v1
                     and (c1.CodeTypeId <>  194480 or c2.CodeTypeId = 7320911) ); -- "from" v2

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

Fiddle to see everything in action.