dunc dunc - 2 months ago 6
MySQL Question

WHERE clause, on joined table, with multiple rows

I have an

incidents
table which has a
1 to many
relationship with a few tables - mainly, for the context of this question,
people
.

Basically, one
incident
may have many
people
(involved).

At the moment, I'm retrieving the
incident
details - plus a concatenated comma-delimited string of people's IDs using this query:

SELECT
i.`ID` AS `id`,
i.`Author_ID` AS `author_id`,
i.`Description` AS `description`,
i.`Date` AS `date`,
i.`Datetime_Created` AS `created`,
p.`Title` AS `period`,
GROUP_CONCAT(DISTINCT ip.`Person_ID` ORDER BY FIELD(ip.`Involvement`, 'V', 'P', 'W') ASC SEPARATOR ',') AS `people_ids`,
( SELECT COUNT(`ID`) FROM `reports` r WHERE r.Incident_ID = i.ID ) AS `reports`,
i.`Status` AS `status`
FROM `incidents` i
LEFT JOIN `reports` ir ON ir.Incident_ID = i.ID
LEFT JOIN `people` ip ON ip.Incident_ID = i.ID
LEFT JOIN `periods` p ON i.Period_ID = p.ID
WHERE 1 NOT IN ( SELECT Category_ID FROM `categories_link` WHERE `Incident_ID` = i.ID )
GROUP BY i.ID
ORDER BY i.`Date` DESC, p.`ID` DESC


This works fine, and produces data like:

Data

What I'm trying to do now is filter these reports so that only incidents where one of the people involved is a student from a certain year group.

This information can be found by joining their IDs to the
students
table. The
students
table contains their
ID
and a
Year_Group
field.

One of the complexities is that some of the IDs from the
people_involved
table may not relate just to students - they could be staff, parents or other members of our community.

I don't want to exclude reports which have other people involved, as long as there is a student from a specific year group involved too.

I've written a query which seems to partially work:

SELECT
i.`ID` AS `id`,
i.`Author_ID` AS `author_id`,
i.`Description` AS `description`,
i.`Date` AS `date`,
i.`Datetime_Created` AS `created`,
p.`Title` AS `period`,
GROUP_CONCAT(DISTINCT ip.`Person_ID` ORDER BY FIELD(ip.`Involvement`, 'V', 'P', 'W') ASC SEPARATOR ',') AS `people_ids`,
( SELECT COUNT(`ID`) FROM `reports` r WHERE r.Incident_ID = i.ID ) AS `reports`,
i.`Status` AS `status`
FROM `incidents` i
LEFT JOIN `reports` ir ON ir.Incident_ID = i.ID
LEFT JOIN `people` ip ON ip.Incident_ID = i.ID
<< LEFT JOIN `student` stu ON ip.Person_ID = stu.db_id >>
LEFT JOIN `periods` p ON i.Period_ID = p.ID
WHERE 1 NOT IN ( SELECT Category_ID FROM `categories_link` WHERE `Incident_ID` = i.ID )
<< AND `stu`.`Year_Group` = 11 >>
GROUP BY i.ID
ORDER BY i.`Date` DESC, p.`ID` DESC


But I just can't imagine that a single simple
JOIN
would be sufficient for the task I'm trying to achieve.

I think a subquery might do it, but I don't know where to begin with that.

The code I would use to access this information (for year 7 students) without all of the necessary
incidents
data would be (I think):

SELECT DISTINCT( p.`Incident_ID` )
FROM `people` p
LEFT JOIN `student` stu ON p.Person_ID = stu.db_id
WHERE stu.Year_Group = 7


How do I bundle that into this code?

Answer

To get incidents where students of only specific age group is included,use the following query.

   SELECT p.Incident_ID 
   FROM people p
   JOIN student stu ON p.Person_ID = stu.db_id
   WHERE stu.Year_Group = 11 
   group by p.Incident_ID 

Your original query returns the incidents and the group of people involved ,So in your original query filter incidents by comparing with the above query written by me.This way you will get all incidents where students from a specific year group involved plus other people also involved(if any).I think this will solve your problem.

 SELECT
      i.`ID` AS `id`,
      i.`Author_ID` AS `author_id`,
      i.`Description` AS `description`,
      i.`Date` AS `date`,
      i.`Datetime_Created` AS `created`,
      p.`Title` AS `period`,
      GROUP_CONCAT(DISTINCT ip.`Person_ID` ORDER BY FIELD(ip.`Involvement`, 'V', 'P', 'W') ASC SEPARATOR ',') AS `people_ids`,
      ( SELECT COUNT(`ID`) FROM `reports` r WHERE r.Incident_ID = i.ID ) AS `reports`,
      i.`Status` AS `status`
    FROM `incidents` i
    LEFT JOIN `reports` ir ON ir.Incident_ID = i.ID
    LEFT JOIN `people` ip ON ip.Incident_ID = i.ID
    LEFT JOIN `periods` p ON i.Period_ID = p.ID
    WHERE 1 NOT IN ( SELECT Category_ID FROM `categories_link` WHERE `Incident_ID` = i.ID )
    and i.ID in //Here you will put the above query
    (
       SELECT p.Incident_ID 
       FROM people p
       JOIN student stu ON p.Person_ID = stu.db_id
       WHERE stu.Year_Group = 11 
       group by p.Incident_ID 
    )
    GROUP BY i.ID
    ORDER BY i.`Date` DESC, p.`ID` DESC