How to get missing parts on trade code table and that exist on table #parts and #search data?

I work on SQL server 2012 I need to count missing rows Part id that exist on table #parts
and not exist on table #tradecode

   create table #searchdata
(
plid int,
codetypeid int
)
insert into #searchdata
(plid,codetypeid)
values
(84459,877490)

create table #parts
(
partid int,
plid int
)
insert into #parts(partid,plid)
values
(758901,84459),
(808091,84459),
(509030,84459),
(7090321,84459),
(32453,84459),
(45563,84459)

create table #tradecode
(
partid int,
codetypeid int
)
insert into #tradecode(partid,codetypeid)
values
(758901,877490),
(808091,877490)

select p.plid,s.codetypeid,count(p.partid) as countmissingParts
from #parts p
inner join #searchdata s on s.plid=p.plid
left join #tradecode t on t.codetypeid=s.codetypeid
where t.partid is null
group by p.plid,s.codetypeid

drop table #searchdata
drop table #parts
drop table #tradecode

Expected result is :slight_smile:

plid        codetypeid    countmissingParts
84459    877490                  4          

actually I put data for search and filter on table searchdata
I need to get data exist on search data table and that exist on parts table
but not exist on table trade code
so it must have 4 parts or rows missing but statement above
return null rows

why and how to solve

hi

hope this helps !!!

please see below

please click arrow to the left for drop create sample data
drop table searchdata
drop table parts 
drop table tradecode 


  create table searchdata
(
plid int,
codetypeid int
)
insert into searchdata
(plid,codetypeid)
values
(84459,877490)

create table parts
(
partid int,
plid int
)
insert into parts(partid,plid)
values
(758901,84459),
(808091,84459),
(509030,84459),
(7090321,84459),
(32453,84459),
(45563,84459)

create table tradecode
(
partid int,
codetypeid int
)
insert into tradecode(partid,codetypeid)
values
(758901,877490),
(808091,877490)
select 
     plid
   , count(partid) 
from 
   parts 
where 
  partid not in ( select partid from tradecode ) 
group by 
   plid 

image
image
image
image

thanks for reply
and where search data table
first thing I get data on table search data by plid and code type id .

second i search for these parts on table parts by plid to get parts related .

third i get missing parts that exist on parts and not exist on trade code table and related to plid on parts table

select 
  * 
from  
  searchdata a 
     join 
  parts b 
      on a.plid = b.plid 
where 
   b.partid not in ( select partid from tradecode ) 

image

You need to change your join to the tradecode table for it to match your description:

select p.plid,s.codetypeid,count(p.partid) as countmissingParts
from #parts p
inner join #searchdata s on s.plid=p.plid
left join #tradecode t on t.partid=s.plid --<<--<<--
where t.partid is null
group by p.plid,s.codetypeid

thank you for reply
when i run this query as posted above

it give me wrong result

plid codetypeid countmissingParts
84459 877490 6

6 is wrong
4 is correct
correct reuslt will be

plid codetypeid countmissingParts
84459 877490 4

so how to modify query to give me 4 not 6

hi

please see my query
.. it will give you Count Missing Parts 4 ...

select 
 plid,codetypeid,count(partid) as countmissingparts 
    from  
      searchdata a 
         join 
      parts b 
          on a.plid = b.plid 
    where 
       b.partid not in ( select partid from tradecode ) 
    group by plid , codetypeid

thanks too much