SQL Outer Join on a bunch of Inner Joined results
- by Matthew Frederick
I received some great help on joining a table to itself and am trying to take it to the next level. The SQL below is from the help but with my addition of the select line beginning with COUNT, the inner join to the Recipient table, and the Group By.
SELECT
Event.EventID AS EventID,
Event.EventDate AS EventDateUTC,
Participant2.ParticipantID AS AwayID,
Participant1.ParticipantID AS HostID,
COUNT(Recipient.ChallengeID) AS AllChallenges
FROM Event
INNER JOIN Matchup Matchup1
ON (Event.EventID = Matchup1.EventID)
INNER JOIN Matchup Matchup2
ON (Event.EventID = Matchup2.EventID)
INNER JOIN Participant Participant1
ON (Matchup1.Host = 1
AND Matchup1.ParticipantID = Participant1.ParticipantID)
INNER JOIN Participant Participant2
ON (Matchup2.Host != 1
AND Matchup2.ParticipantID = Participant2.ParticipantID)
INNER JOIN Recipient
ON (Event.EventID = Recipient.EventID)
WHERE Event.CategoryID = 1
AND Event.Resolved = 0
AND Event.Type = 1
GROUP BY Recipient.ChallengeID
ORDER BY EventDateUTC ASC
My goal is to get a count of how many rows in the Recipient table match the EventID in Event. This code works fine except that I also want to get results where there are 0 matching rows in Recipient. I want 15 rows (= the number of events) but I get 2 rows, one with a count of 1 and one with a count of 2 (which is appropriate for an inner join as there are 3 rows in the sample Recipient table, one for one EventID and two for another EventID).
I thought that either a LEFT join or an OUTER join was what I was looking for, but I know that I'm not quite getting how the tables are actually joined. A LEFT join there gives me one more row with 0, which happens to be EventID 1 (first thing in the table), but that's all. Errors advise me that I can't just change that INNER join to an OUTER. I tried some parenthesizing and some subselects and such but can't seem to make it work.
