Aggregation function with the inner join on sql server-CodePudding

I have 5 tables (A,B,C,D). All are inner joined and used to display data. Another table E has the column sourceId which has reference in column A. I need to show the count of occurrence in the main query. For example:

A.*,B.*,C.*,D.*
FROM A
INNER JOIN B
ON A.BId= B.Id
INNER JOIN C
ON A.CId = C.Id
INNER JOIN D
ON A.Did= D.Id

E table can contain 2 or 1 or 0 records referencing to table A. I need to display count of the occurrence in the above query and sort accordingly from 0 to 2. Can some one help me writing this up? I am not sure how should I go ahead with aggregation function over here. Output should look like this:

E.Count|A.*|B.*|C.*|D.*|

CodePudding user response：

Something like this:

select A.*,B.*,C.*,D.*,E_count
FROM A
INNER JOIN B ON A.BId= B.Id
INNER JOIN C ON A.CId = C.Id
INNER JOIN D ON A.Did= D.Id
outer apply (
   select count(*) as E_count
   from E
   where E.sourceId = A.Id 
) E

select A.*,B.*,C.*,D.*,E_count
FROM A
INNER JOIN B ON A.BId= B.Id
INNER JOIN C ON A.CId = C.Id
INNER JOIN D ON A.Did= D.Id
left join (
   select count(*) as E_count
   from E
   group by E.sourceId 
) E on E.sourceId = A.Id