CODEWITHSUNDEEP
mysql
D.Madu
D.Madu

Reputation: 507

Subselect query with count combining three tables

I have three mysql tables. notification_tbl, section_tbl and user_plant_tbl.

user_plant_tbl has plants and one plant may have multiple sections in section_tbl table. notification_tbl has all closed and open notification with sections. What I want is to get all sections(ex: section name) and counts of all closed notifications from notification_tbl table for that particular section.

section_tbl

section_id      plant
sectionX        2400
sectionY        2400

user_plant_tbl

user_id  plant
3001     2400
3003     2200

notification_tbl

notification_id    plant   section_id      open_flag
60000062           2400    sectionX        x
60000063           2400    sectionX        x
60000064           2400    sectionX        x
60000063           2400    sectionY        x
60000064           2400    sectionY        x

For example lets see sectionX has 3 closed notification rows in notification_tbl table and 2 for sectionY. Then i want to get a output,

sectionX 3
sectionY 2

This is the query I used and it doesn't give me the desired output.

SELECT DISTINCT
    a.section_id,
    (SELECT count(section_id) FROM notification_tbl WHERE open_flag = 'x') AS countx
FROM
    section_tbl AS a
INNER JOIN user_plant_tbl AS b ON a.plant = b.plant
WHERE
    b.user_id = 3001
    AND b.plant = 2400
    AND a.division = 7
GROUP BY
    a.section_id
ORDER BY
    countx DESC;

Upvotes: 2

Views: 120

Answers (1)

marcell
marcell

Reputation: 1528

According to your example there is a section_id column in the notification_tbl too, thus you can relate the section_tbl and the notification_tbl joining them on that column. The following query gives an answer:

SELECT
    s.section_id,
    COUNT(n.section_id) AS count_closed
FROM section_tbl AS s
INNER JOIN notification_tbl AS n ON n.section_id = s.section_id
INNER JOIN user_plant_tbl AS u ON s.plant = u.plant
WHERE
    u.user_id = 3001
    AND u.plant = 2400
    AND s.division = 7
    AND n.open_flag = 'X'
GROUP BY
    s.section_id
ORDER BY
    count_closed DESC;

Update:

Based on your comments I modified the query in order to get those results too.

SELECT
    s.section_id,
    SUM(case when n.open_flag = 'X' then 1 else 0 end) AS count_closed,
    SUM(case when n.open_flag <> 'X' then 1 else 0 end) AS count_opened 
FROM section_tbl AS s
INNER JOIN notification_tbl AS n ON n.section_id = s.section_id
INNER JOIN user_plant_tbl AS u ON s.plant = u.plant
WHERE
    u.user_id = 3001
    AND u.plant = 2400
GROUP BY
    s.section_id
ORDER BY
    count_closed DESC;

See DEMO.

Then take into account the newly inserted sections that have neither opened or closed status flag yet.

SELECT
    s.section_id,
    SUM(CASE WHEN n.open_flag = 'X' THEN 1 ELSE 0 END) AS count_closed
FROM section_tbl AS s
LEFT JOIN notification_tbl AS n ON s.section_id = n.section_id
INNER JOIN user_plant_tbl AS u ON s.plant = u.plant
WHERE
    u.user_id = 3001
    AND u.plant = 2400
GROUP BY
    s.section_id
ORDER BY
    count_closed DESC;

DEMO

Upvotes: 3

Related Questions