So, I was looking at a view we have in our database, and there appears to be a nested junction. I was wondering in what situations that practice would be useful.
In my specific example, it's used to join with a table that has a composite key, but only joining with the tables that have the data as their primary key. For instance, assume a structure like this :
Activity(*IdActivity*, name)
Activity_Week(*IdActivity*, *WeekNo*, subject)
Attendance(*IdPerson*, *IdActivity*, *WeekNo*, isPresent)
Person(*IdPerson*, Name)
In that case, my query would look something like :
SELECT p.Id, p.Name, COUNT(at.*)
FROM Person p
INNER JOIN Activity a
INNER JOIN Activity_Week aw
ON aw.IdActivity = a.IdActivity
INNER JOIN Attendance at
ON at.IdActivity = a.IdActivity
AND at.WeekNo = aw.WeekNo
AND at.isPresent = 1
ON p.IdPerson = at.IdPerson
GROUP BY p.Id, p.Name
So while in this situation I would highly doubt that it's any useful (since you can simply join with attendance and cascade from there), is there any other situation where nested junctions like that are actually a good thing, or would it always be better to join with a request instead?
What I mean by joining with a request would be something along the lines of :
SELECT p.Id, p.Name, COUNT(at.*)
FROM Person p
INNER JOIN (
SELECT a.name, at.*
FROM Activity a
INNER JOIN Activity_Week aw
ON a.IdActivity = aw.IdActivity
INNER JOIN Attendance at
ON at.IdActivity = a.IdActivity
AND at.WeekNo = aw.WeekNo
AND at.isPresent = 1
) attendance_query ON p.IdPerson = attendance_query.IdPerson
GROUP BY p.Id, p.Name
