AlanWilcox

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?