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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

March 27, 2021 at 2:46 am

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

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

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

Site Owners

SSC Guru

Points: 80330
More actions
March 28, 2021 at 3:10 am

#3862831

Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.

SSCrazy

Points: 2157

March 28, 2021 at 3:09 pm

Answer

These 2 queries produce the output you're looking for. It should be said this is not a properly normalized data model. There seemingly are "non-modelled" relationship(s) in the data which mean it's not safe to combine the 2 queries into 1 imo. To generalize the query would require normalizing the data which is exactly what should happen (or already have happened) anyway. The reason convoluted SQL is necessary is because the data model is insufficient. How do you determine which PartId's are 'map to' and which are 'map from'? Maybe there's a lower level of detail than what's being included in the #codes table?

select c.* 
from #codes c
where CodeTypeId=1273200
      and Partlevel=0
      and not exists(select 1
                     from #codes cc
                     where c.PartId=cc.PartId
                           and cc.CodeTypeId=974451
                           and cc.Partlevel<>1)
union all
select c.* 
from #codes c
where CodeTypeId=194480
      and Partlevel=0
      and not exists(select 1
                     from #codes cc
                     where c.PartId=cc.PartId
                           and cc.CodeTypeId=7320911
                           and cc.Partlevel<>1)
order by PartId;

PartIdCodeTypeIdPartlevel
190012732000
22001944800
340012732000
390012732000
50201944800
89001944800
92001944800

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply