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

Question

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

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

Answer 1

If you are learning SQL then you can experiment with other solutions as well. For example:

select p.plid, s.codetypeid, count(p.partid) as countmissingParts
from parts p
inner join (select partid from parts except select partid from tradecode) f on f.partid = p.partid
inner join searchdata s on s.plid=p.plid
group by p.plid,s.codetypeid

Answer 2

;With cte As
(Select s.plid, Count(*) As DesiredCount
From #searchdata s
Inner Join #parts p On p.plid = s.plid
Group By s.plid)

Select s.plid, c.DesiredCount - Count(*) As CountMisssingParts
From #searchdata s
Inner Join cte c On c.plid = s.plid
Left Join #tradecode t On t.codetypeid = s.codetypeid
Left Join #parts p On p.partid = t.partid
Group By s.plid, s.codetypeid, c.DesiredCount
Having c.DesiredCount - Count(*) > 0;

Tom

Answer 3

thank you for reply
code above give me correct result but depend on count different
are there are any way give me missing count without subtract
meaning count missing depend on data it self

Answer 4

 Select s.plid, s.codetypeid, Count(*) As CountMisssingParts
 From #searchdata s
 Inner Join #parts p On p.plid = s.plid
 Left Join #tradecode t On t.partid = p.partid
 Where t.partid Is Null
 Group By s.plid, s.codetypeid;

Tom

Answer 5

Following your thinking, your original query should have written it like this:

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