CODEWITHSUNDEEP
mysqlmysql-5.7
dyesdyes
dyesdyes

Reputation: 1217

Using From (subquery) table in another subquery

So I have the following :

SELECT bt.CompanyName, 
    AcceptedTable.Count AS AcceptedCount,
    CompletedTable.Count AS CompletedCount,
    SkippedTable.Count AS SkippedCount,
    TotalTable.Count AS TotalCount
FROM (
    SELECT uic.*, uic.Id AS UCId, ic.*, ic.Id AS CompanyId, ic.Name AS CompanyName
    FROM UserChallenges iuc 
    JOIN Users iu ON iuc.UserId = iu.Id 
    JOIN Companies ic ON ic.Id = iu.CompanyId) bt
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    GROUP BY CompanyId) TotalTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE AcceptedAt IS NOT NULL
    GROUP BY CompanyId) AcceptedTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE DoneStatus IN (1,6)
    GROUP BY CompanyId) CompletedTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE DoneStatus IN (4)
    GROUP BY CompanyId) SkippedTable ON SkippedTable.CompanyId = bt.CompanyId
GROUP BY bt.CompanyName

The goal is to compute Counts on the same set of data with different WHERE clauses.

I tried the above approach as it simplifies a bit the query instead of having to do JOINs again in each sub queries. But it seems like it's not possible, it's Common table expression and is not available in mysql 5.7.

What would be a better way to implement this ?

There must be a cleaner way than this super long query.

I could use a temporary table too, although I'm not sure it's a good idea in a View.

Upvotes: 1

Views: 25

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You cannot refer another Derived Table alias within a different Derived Table's FROM clause, because it is not accessible at that level.

Also, I feel that you can rather solve this problem using Conditional Aggregation:

SELECT bt.CompanyName, 
       COUNT(CASE WHEN bt.AcceptedAt IS NOT NULL THEN bt.UCId END) AS AcceptedCount,
       COUNT(CASE WHEN bt.DoneStatus IN (1,6) THEN bt.UCId END) AS CompletedCount,
       COUNT(CASE WHEN bt.DoneStatus IN (4) THEN bt.UCId END) AS SkippedCount,
       COUNT(bt.UCId) AS TotalCount
FROM (
    SELECT uic.*, 
           uic.Id AS UCId, 
           ic.*, 
           ic.Id AS CompanyId, 
           ic.Name AS CompanyName
    FROM UserChallenges iuc 
    JOIN Users iu ON iuc.UserId = iu.Id 
    JOIN Companies ic ON ic.Id = iu.CompanyId) bt
GROUP BY bt.CompanyId, bt.CompanyName

Upvotes: 1

Related Questions