CODEWITHSUNDEEP
mysql
tatty27
tatty27

Reputation: 1554

Struggling with MySQL subquery

I have two tables, image and gradeReason. Each image is awarded a grade for it's quality and the user can select up 4 different reasons (reasonID_1, reasonID_2, reasonID_3, reasonID_4) by using selecting a reasonID. The breakdown of the reason is stored in the gradeReason table.

image

imageID   auditID  reasonID_1  reasonID_2  reasonID_3  reasonID_4
-------   -------  ----------  ----------  ----------  ----------
1         123      1           13          7           3
2         124      8           13          8           6 
4         125      3           2           5           6
5         125      7           4           2           3

gradeReason

reasonID   category   name
--------   --------   ----
1          exposure   overexposed
2          exposure   underexposed
3          patient    patient moved
4          equipment  sensor too big
5          equipment  sensor too small

What I would like is a query that will return the number of times each reasonID has been used in an audit and what the name of gradeReason was

e.g.

audit 125 -

reasonID 3 was used twice - name 'patient moved',

reasonID 2 used twice - name 'underexposed'.

I'll be honest and say I have struggled with this for days and I can't even think where to begin.

Upvotes: 0

Views: 76

Answers (3)

Kickstart
Kickstart

Reputation: 21513

Another option using UNIONS:-

SELECT auditId, ReasonId, category, name, SUM(cnt)
FROM
(
    SELECT ir.auditId, gr.ReasonId, gr.category, gr.name, count(*) as cnt
    FROM image ir
    INNER JOIN gradeReason gr
    ON ir.reasonID_1 = gr.ReasonId
    WHERE ir.auditId = 123
    GROUP BY ir.auditId, gr.ReasonId, gr.category, gr.name
    UNION ALL
    SELECT ir.auditId, gr.ReasonId, gr.category, gr.name, count(*) as cnt
    FROM image ir
    INNER JOIN gradeReason gr
    ON ir.reasonID_2 = gr.ReasonId
    WHERE ir.auditId = 123
    GROUP BY ir.auditId, gr.ReasonId, gr.category, gr.name
    UNION ALL
    SELECT ir.auditId, gr.ReasonId, gr.category, gr.name, count(*) as cnt
    FROM image ir
    INNER JOIN gradeReason gr
    ON ir.reasonID_3 = gr.ReasonId
    WHERE ir.auditId = 123
    GROUP BY ir.auditId, gr.ReasonId, gr.category, gr.name
    UNION ALL
    SELECT ir.auditId, gr.ReasonId, gr.category, gr.name, count(*) as cnt
    FROM image ir
    INNER JOIN gradeReason gr
    ON ir.reasonID_4 = gr.ReasonId
    WHERE ir.auditId = 123
    GROUP BY ir.auditId, gr.ReasonId, gr.category, gr.name
) Sub1
GROUP BY auditId, ReasonId, category, name

Upvotes: 0

trapicki
trapicki

Reputation: 2020

For your select, you have to join gradeReason once for every foreign key to gradeReason:

    SELECT imageID
         , auditID
         , r1.name
         , r2.name
         , r3.name
         , r4.name
     FROM image i
LEFT JOIN gradeReason r1 on i.reasonID_1 = r1.reasonID
LEFT JOIN gradeReason r2 on i.reasonID_2 = r2.reasonID
LEFT JOIN gradeReason r3 on i.reasonID_3 = r3.reasonID
LEFT JOIN gradeReason r4 on i.reasonID_4 = r4.reasonID

Your example data is not sensible, as the foreign keys to gradeReason in your table image is not in gradeReason, but I guess you have more gradeReasons up your sleeve.

Moreover,your structure is not normalized: gradeReason has duplicate entries in rows for category. This is not too bad, but it would be good practice to have a separate gradeReasonCategories table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is harder because your data is not properly normalized. The following approach first normalizes the data, then does the join and aggregation:

select ir.auditId, gr.ReasonId, gr.category, gr.name, count(*) as cnt
from (select i.imageID, i.auditID,
             (case when n.n = 1 then ReasonID_1
                   when n.n = 2 then ReasonID_2
                   when n.n = 3 then ReasonID_3
                   when n.n = 4 then ReasonID_4
              end) as ReasonId
      from image i cross join
           (select 1 as n union all select 2 union all select 3 union all select 4
           ) n
      ) ir join
      gradeReason gr
      on ir.ReasonId = gr.ReasonId
group by ir.auditId, gr.ReasonId, gr.category, gr.name
order by cnt desc;

Upvotes: 5

Related Questions