Question 21242 in Mysql Question posted in
The official MySQL documentation can be found here.

Group by Use Query if SQL LEFT JOIN is performed multiple times – Mysql

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?

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search