Running the below query returns the results i'm after for 1 record
select
D.VDN AS 'ParentUsage',
p.id AS 'ParentIntPart',
pr.VPRN AS 'ParentPartNo',
s.vSC AS 'ParentSuffix',
pr.vPD AS 'ParentPartDesc',
pr.vPNMR AS 'ParentMajRev',
pr.tPNMR AS 'ParentMinRev',
from [dbo].[tblD] D
join [dbo].[lnk_D_P] DP on DP.intDID = D.id
join [dbo].[lnk_P_PA] PA on D.intPAID =PA.id
join [dbo].[lkpSuffix] S on D.tPNSID = s.ID
join [dbo].[tblP] P on P.id=DP.intPartID
join [dbo].[tblPR] PR on P.id=PR.ID
(select count (*) from [dbo].[tblD] where [iPID] =48926) as 'Childcount'
where d.blnInActive =0 and d.varDocumentNumber='120USE03375' order by [ParentUsage]
the problem i have is i need to Childcount column to run on all off the database and not just 1 records.
i think it's a problem with my join but i'm not 100% sure
i need the childcount to count all of the duplicates in ParentIntPart
anyone able to advise?