Group by Use Query if SQL LEFT JOIN is performed multiple times-CodePudding

I think it's impossible, but I'm asking if there's a good way.

There are A table / B table / C table.

The table was joined LEFT JOIN based on table A with FK called id of each table.

At this time, I would like to output the count(*) as B table rows and C table rows based on b.id(B_CNT) c.id(C_CNT)

SELECT 
* 
FROM 
A 
LEFT JOIN B ON A.ID = B.ID 
LEFT JOIN C ON A.ID = C.ID (base query)

how could I count group by b.id and c.id?

CodePudding user response：

You could try:

SELECT 
COUNT(DISTINCT B.ID), COUNT(DISTINCT C.ID)
FROM A 
LEFT JOIN B 
ON A.ID = B.ID 
LEFT JOIN C 
ON A.ID = C.ID

(I couldn't quite understand from your question, but I'm making an assumption that you want the distinct count of "ID" from each table)

CodePudding user response：

You can use a couple of scalar subqueries. For example:

select id, 
  (select count(*) from b where b.id = a.id) as b,
  (select count(*) from c where c.id = a.id) as c
from a