SQL double outer join?

Question

I have the following tables:

Users - Users of the site
Matchups - Matchups between two teams
Periods - The periods in which matchups take place
MatchupResults - The results of the matchups
UserPicks - The user's choice of who will win the matchup (5 picks for each period) 
Teams - Teams in the matchups

The relationships are as follows:

Matchups.HomeID/Matchups.VisitorID = Teams.ID
Matchups.PeriodID = Periods.ID
MatchupResults.MatchupID = Matchups.ID
UserPicks.MatchupID = Matchups.ID
UserPicks.UserID = User.ID

What I am trying to get (in english) is:

All users regardless of whether they have made picks and all matchups regardless of whether they have results yet. If they have results or picks, I want them shown.

I've gotten this query to get me the matchups as I want them:

select P.ID as PeriodID,
       M.ID as MatchupID,
       V.ID as VisitorTeam,
       H.ID as HomeTeam,
       MR.VisitorScore,
       MR.HomeScore,
       CASE WHEN (M.PointSpread <= 0) THEN
                    CASE WHEN (MR.HomeScore + M.PointSpread > MR.VisitorScore) THEN H.ID ELSE V.ID END
             WHEN (M.PointSpread > 0) THEN
                    CASE WHEN (MR.HomeScore + M.PointSpread < MR.VisitorScore) THEN V.ID ELSE H.ID END
             ELSE NULL
        END AS TeamThatCoveredSpread
from Matchups M
left outer join MatchupResults MR ON MR.MatchupID = M.ID
inner join Teams V ON V.ID = M.VisitorID
inner join Teams H ON H.ID = M.HomeID
inner join Periods P ON P.ID = M.PeriodID

However I'm having trouble including the users and their picks - no matter what I try it keeps filtering my results to only games that users have picked.

So if there are 10 users and I select 1 matchup, I should see all 10 users and either a TeamID as their pick or a NULL meaning they did not make a pick for that game.

UPDATE: Sample results for above query with Period 1 specified for simplicity:

Answer 1

I answered my problem thanks to your suggestions and have marked up each of your answers and comments for your time.

Thank you for your valuable time, here is the answer and result set for a single period/user (filtered only for simplicity here):

insert #UserMatchups
select  U.ID, 
        M.ID, 
        M.VisitorID,
        M.HomeID,
        M.HomeSpread
from Users U, Matchups M
where PeriodID = @PeriodID

select  M.UserID, 
        M.MatchupID,
        CASE WHEN (M.Spread <= 0) THEN
                    CASE WHEN (MR.HomeScore + M.Spread > MR.VisitorScore) THEN M.HomeID ELSE M.VisitorID END
             WHEN (M.Spread > 0) THEN
                    CASE WHEN (MR.HomeScore + M.Spread < MR.VisitorScore) THEN M.VisitorID ELSE M.HomeID END
             ELSE NULL
        END AS TeamThatCoveredSpread,
        UP.TeamID AS UserPick 
from #UserMatchups M
left outer join MatchupResults MR ON MR.MatchupID = M.MatchupID
left outer join UserPicks UP ON UP.UserID = M.UserID and UP.MatchupID = M.MatchupID
where M.UserID = 1

Answer 2

how about this:

;WITH matchups (periodid, matchupid, vistortteam,HomeTeam,VisitorScore, HomeScore)
(
SELECT P.ID AS PeriodID
, M.ID AS MatchupID
, V.ID AS VisitorTeam
, H.ID AS HomeTeam
, MR.VisitorScore
, MR.HomeScore
,CASE   WHEN (M.PointSpread <= 0) 
        THEN
                     CASE WHEN (MR.HomeScore + M.PointSpread > MR.VisitorScore) 
                        THEN H.ID 
                     ELSE V.ID END                
        WHEN (M.PointSpread > 0) 
        THEN  
            CASE WHEN (MR.HomeScore + M.PointSpread < MR.VisitorScore) 
                THEN V.ID 
            ELSE H.ID END              
        ELSE NULL         
        END AS TeamThatCoveredSpread 
FROM Matchups M 
LEFT OUTER JOIN MatchupResults MR ON MR.MatchupID = M.ID 
INNER JOIN Teams V ON V.ID = M.VisitorID 
INNER JOIN Teams H ON H.ID = M.HomeID 
INNER JOIN Periods P ON P.ID = M.PeriodID 
)

SELECT *
FROM  USERS u
JOIN USERPIcks up ON u.id = up.userid
LEFT JOIN matchups m  ON m.MatchupID = up.MatchupID

Answer 3

I think you should start with users and then LEFT JOIN out. If you really want to INNER JOIN you can do that but you'll need to put it inside Paranes. Its also probably a good idea to include @Matchupid in the JOIN CLAUSE as well since that way you won't have to worry about picking up other matchups.

This should work (parsed but not tested)

SELECT 
   P.ID as PeriodID,
   M.ID as MatchupID,
   V.ID as VisitorTeam,
   H.ID as HomeTeam,
   MR.VisitorScore,
   MR.HomeScore,
   CASE WHEN (M.PointSpread <= 0) THEN
                CASE WHEN (MR.HomeScore + M.PointSpread > MR.VisitorScore) THEN H.ID ELSE V.ID END
         WHEN (M.PointSpread > 0) THEN
                CASE WHEN (MR.HomeScore + M.PointSpread < MR.VisitorScore) THEN V.ID ELSE H.ID END
         ELSE NULL
    END AS TeamThatCoveredSpread
FROM   users u 
       LEFT JOIN userpicks up 
         ON u.id = up.user_id 
       LEFT JOIN matchups m 
         ON up.matchupid = m.id 
            AND up.matchupid = @Matchupid 
       LEFT OUTER JOIN (matchupresults mr 
                        INNER JOIN teams v 
                          ON v.id = m.visitorid 
                        INNER JOIN teams h 
                          ON h.id = m.homeid 
                        INNER JOIN periods p 
                          ON p.id = m.periodid) 
         ON mr.matchupid = m.id 

Answer 4

The problem here is that you have INNER JOINs which depend on a match to a LEFT OUTER JOIN so, naturally, if there are no results from your LEFT OUTER JOIN then the INNER JOIN cannot return results.
You need to eliminate your INNER JOINs because they require data in MatchupResults

make each of them a LEFT OUTER JOIN then put their JOIN conditions in the WHERE clause like this....

WHERE 
    (M.[VisitorID] IS NULL OR V.[ID] = M.[VisitorID])
    AND (M.[HomeID] IS NULL OR H.[ID] = M.[HomeID])
    AND (M.[PeriodID] IS NULL OR P.ID = M.[PeriodID])

So now your JOIN will proceed but only be effective when MatchupResults is JOINed