SQL where subquery not filtering like expected -

i have sql query below. want part result not return rows categorysite returns more 2 rows matched categoryid in productcategory regardless of siteid. problem know productcategory has more 1 matching categories of results receiving, there wrong query , can't figure out what.

select top 10 pp.* productproperty pp  inner join productcategory pc on pp.fkproductid = pc.fkproductid , pp.fklocaleid = 1 inner join categorysite cs on pc.fkcategoryid = cs.fkcategoryid , cs.fksiteid = 2 inner join categoryproperty cp on cs.fkcategoryid = cp.fkcategoryid , cp.fklocaleid=1   (select count(*) categorysite css pc.fkcategoryid = css.fkcategoryid) = 1 

why joining categoryproperty if don't use in predicate?

try this. if need categoryproperty, try adding @ end

;with insteadofwhere  (     select fkcategoryid      categorysite      group fkcategoryid      having count(fkcategoryid) = 1   ) select top 10 * productproperty pp  inner join productcategory pc on pp.fkproductid = pc.fkproductid , pp.fklocaleid = 1 inner join insteadofwhere on insteadofwhere.fkcategoryid = pc.fkcategoryid  inner join categorysite cs on insteadofwhere.fkcategoryid = cs.fkcategoryid , cs.fksiteid = 2