Multiple INNER JOIN subqueries sql

inner-join, mysql, select, sql, subquery

Solution

SELECT
        studid,
        studname,
        status,
        presentcnt,
        status1,
        participatedcnt FROM
(SELECT
        classlist.studentid studid,
        student.name studname,
        classStatus.name status,
        COUNT(*) presentcnt
    FROM
        classlist 
        INNER JOIN student ON classlist.studentid=student.id
        INNER JOIN classstatus ON classlist.presentid=classstatus.id
        INNER JOIN (
            SELECT
                studentid,
                max(CASE WHEN presentid=0 THEN id END)  max_0,
                max(CASE WHEN presentid=1 THEN id END)  max_1
            FROM classlist
            GROUP BY studentid
        ) s
        ON coalesce(classlist.id>least(max_0,max_1) AND classlist.id<=greatest(max_0,max_1),1) AND s.studentid=classlist.studentid
    GROUP BY classlist.studentid)x
JOIN

(SELECT
        classlist.studentid,
        student.name,
        participatedStatus.name status1,
        COUNT(*) participatedcnt
    FROM
        classlist 
        INNER JOIN student ON classlist.studentid=student.id
        INNER JOIN participatedStatus ON classlist.participatedid=participatedStatus.id
        INNER JOIN (
            SELECT
                studentid,
                max(CASE WHEN participatedid=0 THEN id END)  max_0,
                max(CASE WHEN participatedid=1 THEN id END)  max_1
            FROM classlist
            group by studentid
        ) s
        ON coalesce(classlist.id>least(max_0,max_1) 
        AND classlist.id<=greatest(max_0,max_1),1)
        AND s.studentid=classlist.studentid
    group by classlist.studentid)y
ON x.studid=y.studentid

Fiddle

Problem

I have the below query which comes from this post count number of items in a row in mysql which counts how many times in a row a student has been present/absent from a class. ``` SELECT classlist.studentid, student.name, classStatus.name status, COUNT(*) presentcnt FROM classlist INNER JOIN student ON classlist.studentid=student.id INNER JOIN classstatus ON classlist.presentid=classstatus.id INNER JOIN ( SELECT studentid, max(CASE WHEN presentid=0 THEN id END) max_0, max(CASE WHEN presentid=1 THEN id END) max_1 FROM classlist GROUP BY studentid ) s ON coalesce(classlist.id>least(max_0,max_1) AND classlist.id<=greatest(max_0,max_1),1) AND s.studentid=classlist.studentid GROUP BY classlist.studentid ``` This works as expected, ``` STUDENTID NAME STATUS PRESENTCNT 111 John Present 1 222 Kate Absent 2 333 Matt Present 5 ``` I want to extend the query so that I have a column showing if the student particpated in the class. If I run an independent query I get the results I want ``` SELECT classlist.studentid, student.name, participatedStatus.name status, COUNT(*) participatedcnt FROM classlist INNER JOIN student ON classlist.studentid=student.id INNER JOIN participatedStatus ON classlist.participatedid=participatedStatus.id INNER JOIN ( SELECT studentid, max(CASE WHEN participatedid=0 THEN id END) max_0, max(CASE WHEN participatedid=1 THEN id END) max_1 FROM classlist group by studentid ) s ON coalesce(classlist.id>least(max_0,max_1) AND classlist.id<=greatest(max_0,max_1),1) AND s.studentid=classlist.studentid group by classlist.studentid STUDENTID NAME STATUS PARTICIPATEDCNT 111 John Yes 1 222 Kate No 2 333 Matt Yes 2 ``` However I want to merge them into the one query so I get ``` STUDENTID NAME STATUS PRESENTCNT STATUS2 PARTICIPATEDCNT 111 John Present 1 Yes 1 222 Kate Absent 2 No 2 333 Matt Present 5 Yes 2 ``` I am confused about how this can be achieved as I am selecting count *, how can I acheive this? A sample of the data I am using is in this fiddle and below ``` CREATE TABLE classlist (`id` int, `studentid` int, `subjectid` int, `presentid` int, `participatedid` int); CREATE TABLE student (`id` int, `name` varchar(4)); CREATE TABLE subject (`id` int, `name` varchar(4)); CREATE TABLE classStatus (`id` int, `name` varchar(8)); CREATE TABLE participatedStatus (`id` int, `name` varchar(8)); INSERT INTO classlist (`id`, `studentid`, `subjectid`, `presentid`, `participatedid`) VALUES (1, 111, 1, 1, 0), (2, 222, 3, 0, 0), (3, 333, 2, 1, 0), (4, 111, 4, 0, 0), (5, 111, 1, 1, 0), (6, 222, 3, 0, 0), (7, 333, 2, 1, 1), (8, 111, 4, 0, 0), (9, 111, 4, 0, 0), (10, 111, 4, 0, 0), (11, 111, 1, 1, 1), (12, 333, 3, 1, 0), (13, 333, 2, 1, 1), (14, 333, 3, 1, 1); INSERT INTO student (`id`, `name`) VALUES (111, 'John'),(222, 'Kate'),(333, 'Matt'); INSERT INTO subject (`id`, `name`) VALUES (1, 'MATH'),(2, 'ENG'),(3, 'SCI'),(4, 'GEO'); INSERT INTO classStatus (`id`, `name`) VALUES (0, 'Absent'), (1, 'Present'); INSERT INTO participatedStatus (`id`, `name`) VALUES (0, 'No'),(1, 'Yes'); ```

Original source

Related problems