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

Is it possible to use "SELECT COUNT" as a reference to another cell with the "JOIN ON" clause in MySQL?

I have a problem as follows; Let’s say I have 3 tables like this:

tableA

ID_name Name Email
1 Alice xx
2 Betty xx
3 Chris xx

tableB

Id_task id_name Tasks_submitted
1 1 1
2 2 1
3 1 1
4 2 1
5 1 1

tableC

Id_C Task_count score
1 0 E
2 1 D
3 2 C
4 3 B
5 4 A

Then I execute this query:

Select name,COUNT(Tasks_submitted) as TASKS, score
from tableA
LEFT JOIN tableB on tableA.ID_name=tableB.id_name
JOIN tableC ON (SELECT COUNT (Tasks_submitted)) = tableC.Task_count
GROUP BY tableA.ID_name

But that doesn’t work.

The result I want:

Name TASKS score
Alice 3 B
Betty 2 C
Chris 0 E

Is it possible in SQL to use "SELECT COUNT" or alias as a reference to another table in the JOIN ON clause? Or is there another way to achieve the query result I desire? Many Thanks.

Tags:

2

Answers


  1. 0 
    SELECT sub.name, sub.TASKS, t2.score
FROM (
    SELECT name, COUNT(Tasks_submitted) as TASKS
    FROM tableA
    LEFT JOIN tableB ON tableA.ID_name = tableB.id_name
    GROUP BY tableA.ID_name
) AS sub
LEFT JOIN tableC AS t2 ON sub.TASKS = t2.Task_count;
    Login or Signup to reply.
  2. 0

    You need to aggregate the tableB (tasks) data in a derived table, so the count is available for the join:

    SELECT a.Name, IFNULL(b.cnt, 0) AS TASKS, c.score
FROM tableA a
LEFT JOIN (
    SELECT id_name, COUNT(*) AS cnt
    FROM tableB
    GROUP BY id_name
) b ON a.ID_name = b.id_name
LEFT JOIN tableC c
    ON IFNULL(b.cnt, 0) = c.Task_count;

    You have not included much of an explanation, so we do not know what the possible values for Tasks_submitted are. If it can only be 1 then COUNT(*) is slightly more efficient, as it is not null checking the column.

    Assuming there is an index on tableB.id_name, doing the aggregation on just tableB in the derived table should be more efficient than having both tableA and tableB in the derived table, but test with your data to verify either way.

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search