Select on a Cross Join Returning Wrong Results

Question

****EDIT**** Adding SQL Fiddle Link HERE I created the scenario in SQL Fiddle to show what I am trying to accomplish. I found it very odd that it produces accurate results in SQL Fiddle yet produces the results below using my DB.

However for the sake of proving that the seasons truly exist here is a select tvseasons join on tvseries:

Running this query:

SELECT TVSeriesHasTVSeason.tvSeriesID, TVSeason.tvSeasonID, TVSeason.title, Users.userID, 
    CASE
        WHEN UserHasWatchedTVSeason.tvSeasonID IS NULL THEN 'No'
        ELSE 'Yes'
    END as watched
FROM TVSeason
CROSS JOIN Users
LEFT JOIN UserHasWatchedTVSeason
    ON  TVSeason.tvSeasonID = UserHasWatchedTVSeason.tvSeasonID
    AND Users.userID = UserHasWatchedTVSeason.userID
RIGHT JOIN TVSeriesHasTVSeason
    ON TVSeason.tvSeasonID = TVSeriesHasTVSeason.tvSeasonID 

Returns:

515 1769    1000 Ways to Die Season 1   3   Yes
515 1770    1000 Ways to Die Season 2   3   Yes
515 1772    1000 Ways to Die Season 4   3   Yes
515 1773    1000 Ways to Die Season 5   3   Yes
516 1774    2 Stupid Dogs Season 1      3   No
516 1775    2 Stupid Dogs Season 2      3   No
517 1777    24 Season 2                 3   Yes
517 1779    24 Season 4                 3   Yes
517 1780    24 Season 5                 3   Yes
517 1781    24 Season 6                 3   Yes
517 1782    24 Season 7                 3   Yes

The season id are consecutive you can clearly see season 3 of 1000 ways to die is not being returned and there are also a couple seasons of 24 also not being returned.

I have no idea what is wrong with this query?

****EDIT** I believe I found a working solution to the issue:**

SELECT x.*,
    CASE
        WHEN UserHasWatchedTVSeason.tvSeasonID IS NULL THEN 'No'
        ELSE 'Yes'
    END as watched
FROM
(SELECT 
    TVSeries.tvSeriesID, TVSeries.title,
    TVSeriesHasTVSeason.tvSeasonID,
    Users.userID
FROM TVSeries
LEFT JOIN TVSeriesHasTVSeason
    on tvseries.tvSeriesID = TVSeriesHasTVSeason.tvSeriesID
LEFT JOIN TVSeason
    on tvseason.tvSeasonID = TVSeriesHasTVSeason.tvSeriesID
CROSS JOIN Users)x
LEFT JOIN UserHasWatchedTVSeason
    on x.tvSeasonID = UserHasWatchedTVSeason.tvSeasonID
    AND x.userID = UserHasWatchedTVSeason.userID

Answer 1

This is a working solution:

SELECT x.*,
    CASE
        WHEN UserHasWatchedTVSeason.tvSeasonID IS NULL THEN 'No'
        ELSE 'Yes'
    END as watched
FROM
    (SELECT 
        TVSeries.tvSeriesID, TVSeries.title,
        TVSeriesHasTVSeason.tvSeasonID,
        Users.userID
    FROM TVSeries
    LEFT JOIN TVSeriesHasTVSeason
        on tvseries.tvSeriesID = TVSeriesHasTVSeason.tvSeriesID
    LEFT JOIN TVSeason
        on tvseason.tvSeasonID = TVSeriesHasTVSeason.tvSeriesID
    CROSS JOIN Users)x
LEFT JOIN UserHasWatchedTVSeason
    on x.tvSeasonID = UserHasWatchedTVSeason.tvSeasonID
    AND x.userID = UserHasWatchedTVSeason.userID

My thought is that in my original post that I was losing my connection in the CROSS JOIN when I referenced it in later JOINS.

I would hope someone might be able to tell me WHY this worked exactly as it is still a little unclear to me.

Also to expand my answer to return 'yes', 'no', 'partially watched':

SELECT *
FROM
    (SELECT userID, tvSeriesID, 
        CASE
            WHEN COUNT(tvSeriesID) = ABS(SUM(watched)) 
                AND SUM(watched) > 0 THEN 'Yes'
            WHEN COUNT(tvSeriesID) = ABS(SUM(watched)) 
                AND SUM(watched) < 0 THEN 'No'
            ELSE 'Partial'
        END as watched
    FROM
        (SELECT x.*,
            CASE
                WHEN UserHasWatchedTVSeason.tvSeasonID IS NULL THEN -1
                ELSE 1
            END as watched
        FROM
            (SELECT 
                TVSeries.tvSeriesID, TVSeries.title as tvSeriesTitle,
                TVSeriesHasTVSeason.tvSeasonID, 
                Users.userID
            FROM TVSeries
            LEFT JOIN TVSeriesHasTVSeason
                on tvseries.tvSeriesID = TVSeriesHasTVSeason.tvSeriesID
            LEFT JOIN TVSeason
                on tvseason.tvSeasonID = TVSeriesHasTVSeason.tvSeriesID
            CROSS JOIN Users
            )x
        LEFT JOIN UserHasWatchedTVSeason
            on x.tvSeasonID = UserHasWatchedTVSeason.tvSeasonID
            AND x.userID = UserHasWatchedTVSeason.userID
        )y
    GROUP BY userID, tvSeriesID
    )z
ORDER BY userID, tvSeriesID