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