SQL Query for multiple joined tables

I am querying against a SQL Server database and I’ve been struggling to get the result set I want. I feel like it is possible but I’m just unable to do it. Here is how the DB is layed out.

City Table: 
CityID       CityName       StateID
------       --------       -------
1            Fairfax        1
2            Richmond       1
3            Roanoke        1

State Table: 
StateID      StateName      StateShortName
-------      ---------      --------------
1            Virginia       VA

Score Table: 
CityID       StateID        Score
------       -------        -----
1            1              50
3            1              100

I’d like to return a result set that looks like this:

CityID       CityName       StateID        StateName     StateShortName    Score
------       --------       -------        ---------     --------------    -----
1            Fairfax        1              Virginia      VA                50
2            Richmond       1              Virginia      VA                NULL
3            Roanoke        1              Virginia      VA                100

I’ve unsuccessfully tried using CTE‘s:

;WITH CTE AS 
(
    SELECT DISTINCT C.CityID, 
        C.CityName, 
        S.StateID, 
        S.StateName, 
        S.StateShortName, 
        S.CountryID 
    FROM City C WITH (NOLOCK) 
    INNER JOIN State S WITH (NOLOCK) ON C.StateID = S.StateID
    WHERE C.StateID = 4
)
SELECT SC.CityID, CTE.CityName, SC.StateID, CTE.StateName, CTE.StateShortName, SC.Score
FROM Score SC WITH (NOLOCK)
INNER JOIN CTE ON SC.StateID = CTE.CityID
ORDER BY CTE.CityID ASC

I’ve also unsuccessfully tried UNION‘s:

SELECT C.CityID, 
    C.CityName, 
    S.StateID, 
    S.StateName, 
    S.StateShortName, 
    CASE 
        WHEN SC.Score = NULL 
        THEN NULL
        ELSE SC.Score
    END AS Score
FROM Score SC WITH (NOLOCK)
INNER JOIN State S WITH (NOLOCK) ON SC.StateID = S.StateID
INNER JOIN City C WITH (NOLOCK) ON SC.CityID = C.CityID
WHERE SC.StateID = 3
UNION 
SELECT C.CityID, 
    C.CityName, 
    S.StateID, 
    S.StateName, 
    S.StateShortName, 
    NULL AS Score
FROM City C WITH (NOLOCK)
INNER JOIN State S WITH (NOLOCK) ON C.StateID = S.StateID
WHERE S.StateID = 3
ORDER BY C.CityID ASC

Any help would be appreciated!

Answer

You need to LEFT JOIN the Score table:

SELECT C.CityID, 
    C.CityName, 
    S.StateID, 
    S.StateName, 
    S.StateShortName, 
    SC.Score
FROM City C
INNER JOIN State S ON S.StateID = C.StateID
LEFT JOIN Score SC ON SC.StateID = S.StateID AND SC.CityID = C.CityID;

Advertisement

Answer