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

Transact-SQL
,
1

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

2

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

3

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

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

image

5

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
6

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

7

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
8

thanks too much