[SOLVED] Trouble with query using many JOINS

[annihilate]

Hi

MySQL server version is: 4.1.22

 

I am having some trouble writing a query.  First off, I will show the table structure:

 

Table: tv_seasons

Fields: season_id, show_id, season, episodes

 

season_id is a primary key, auto increment value.  show_id corresponds with an id from the tv_show_names table.  season is the season number, and episodes is the total number of episodes for that season.

 

Table: tv_seasons_watched

Fields: season_id, episodes, user_id

 

season_id corresponds to a season_id from the tv_seasons table.  episodes is the number of epsidoes the user has watched of that season.  user_id is the user_id of the person who has watched that season.

 

Table: tv_shows_tracked

Fields: user_id, show_id

 

user_id is the user_id of the user and show_id corresponds to a show_id from the tv_show_names table.

 

Table: tv_show_names

Fields: show_id, show_name, date_added

 

show_id is primary key, auto incremented value.  show_name is the name of the show, date_added is the date this show was added to the database.

 

So there are some TV show names in the tv_show_names table.

A user can then select which of these shows they want to keep track of.  This updates the tv_shows_tracked table.

A season for a TV show is added to the tv_seasons table.

If a user is tracking a show, and some seasons exist for that show, they can then select how many episodes of that TV show they have watched. This updates the tv_seasons_watched table.

 

The query I want to perform is as follows:

 

For a user, I want to display the show names they are tracking which have exisitng seasons, and show how many episodes they have watched.  I thought I had it working until I logged in as another user, and saw that it was not working correctly.

 

The current query:

 

SELECT *, tv_seasons.*, tv_show_names.show_name, tv_seasons_watched.episodes AS num_watched, tv_seasons_watched.user_id FROM tv_shows_tracked
LEFT JOIN tv_show_names ON tv_shows_tracked.show_id = tv_show_names.show_id 
LEFT JOIN tv_seasons_watched ON tv_seasons_watched.season_id = tv_seasons.season_id 
INNER JOIN tv_seasons ON tv_seasons.show_id = tv_shows_tracked.show_id 
WHERE tv_shows_tracked.user_id = '1'
ORDER BY show_name, season 

Note: the user_id of 1 in above code is dynamic in the actual query, taking the value of the current logged in user.

 

This seems to work when I am logged in with user_id of 1.  However, when logged in as user_id 3, it will show the results of user id 1 for the shows that user_id 3 is tracking.  So for example, if both users are tracking show_id 4, and user_id 1 has watched all 20 episodes of season 1 of show_id 4, then it will display as user_id 3 having also watched all 20 episodes.

 

Hopefully anyone reading this understands. If not please let me know and I'll try and provide more info.

 

Thanks

Colin

[fenway]

Could you explain this again?

 

This seems to work when I am logged in with user_id of 1.  However, when logged in as user_id 3, it will show the results of user id 1 for the shows that user_id 3 is tracking.  So for example, if both users are tracking show_id 4, and user_id 1 has watched all 20 episodes of season 1 of show_id 4, then it will display as user_id 3 having also watched all 20 episodes.

[annihilate]

Basically, currently, I have 28 TV shows in the tv_show_names table.  I have 32 seasons added in the tv_seasons table, as some TV shows have more than one season.  I log in as user_id 1 and select the TV shows I want to track out of the possible 28.  I select them all, so I am now tracking all shows.  So when I ran that query for the first time, it returned 32 rows (as expected, since there are 32 seasons added and I am tracking every show), the value for num_watched was NULL (also correct so far).  I then updated what episodes I had watched.  So for example, I have show_id with value 2.  This has 6 seasons associated with it.  I update it so that the tv_seasons_watched table has 6 entries in it saying user_id 1 has watched 24 episodes of season_id's 3, 4, 5, 6, 7, and 8.

 

I then log out of that user and log in as someone else.  user_id 3.  I select the TV shows I want to track.  I select only one show, show_id 2.  Both user_id 1 and user_id 3 are now tracking this TV show.  I then run that query and it returns me 6 rows.  Again, this is the correct number of rows, since show_id 2 has 6 seasons associated with it. (season_id's 3,4,5,6,7 and 8 ).  However, the problem is that the num_watched value is returning 24 for all 6 episodes.  This is what user_id 1 has watched, however, user_id 3 has not watched any episodes and has no rows in the tv_seasons_watched table, and therefore should be returning NULL for num_watched.

 

Hope that explains it.

 

Thanks

Colin

[fenway]

Well, I assume the watche table has a user_id... I don't see this in any join condition / where clause.

[annihilate]

Hi, have actually just got this fixed.  Someone over at Devshed posted the correct query to use, which ended up being this:

 

SELECT tv_show_names.show_id,
	tv_show_names.show_name,
	tv_seasons.season,
	tv_seasons.season_id,
	tv_seasons.episodes as total_episodes,
	tv_seasons_watched.episodes as watched
	FROM tv_show_names
	LEFT JOIN tv_seasons ON tv_show_names.show_id = tv_seasons.show_id
	LEFT JOIN tv_seasons_watched ON tv_seasons.season_id = tv_seasons_watched.season_id
	AND tv_seasons_watched.user_id = 3
	LEFT JOIN tv_shows_tracked ON tv_seasons.show_id = tv_shows_tracked.show_id
	WHERE tv_shows_tracked.user_id = 3
	ORDER BY show_name, season

The addition of the AND clause was basically the fix, and the query has been tidied up/rearraged as well.

[fenway]

Yes, that's the extra part of the ON clause that I was referring to.  In the future, don't post the same issue on other forums... otherwise, why should I/we spend any time here if you're just going to waste our time?