Joining Two Tables, but how to show all results?

I have two table structures like these:

episodes (
episode_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
episode_series_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
episode_series_season TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
episode_series_episode SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
episode_title VARCHAR(200) NOT NULL DEFAULT '',
PRIMARY KEY (episode_id),
KEY series_id (episode_series_id),
KEY episode_series_season (episode_series_season),
KEY episode_series_episode (episode_series_episode)
) ENGINE=MyISAM;

watchlist (
watchlist_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
watchlist_user_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
watchlist_series_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
watchlist_series_episode_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
watchlist_status TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (watchlist_id),
KEY watchlist_series_episode_id (watchlist_series_episode_id),
KEY watchlist_series_id (watchlist_series_id)
) ENGINE=MyISAM;"

I want to join these two, and show all results.

This didn't help me:

SELECT e.*, w.* FROM episodes e, watchlist w WHERE e.episode_series_id='1' AND e.episode_series_season='1' AND w.watchlist_user_id='1'

I'm using this query, but as usual, it shows only matching columns (30+ lines in episodes table, 2 lines in watchlist table)

SELECT e.*,w.* FROM episodes e INNER JOIN watchlist w ON e.episode_id=w.watchlist_series_episode_id WHERE e.episode_series_id='1' AND e.episode_series_season='1' AND w.watchlist_user_id='1'

But it only shows 2 lines (as it should).

episode_id  episode_series_id   episode_series_season   episode_series_episode  episode_title   watchlist_id    watchlist_user_id   watchlist_series_id watchlist_series_episode_id watchlist_status
1                 1                       1                      1                    Pilot            1                 1                    1                    1                        0
2                 1                       1                      2                 Paternity           2                 1                    1                    2                        0 

But I want to show all matching results (I want to select all columns from these two table, list episodes, if there's a match from watchlist I want to fill data from watchlist column, else they should be null)

I want to get results like this:

episode_id  episode_series_id   episode_series_season   episode_series_episode  episode_title   watchlist_id    watchlist_user_id   watchlist_series_id watchlist_series_episode_id watchlist_status
1                 1                       1                      1                    Pilot            1                 1                    1                    1                        0
2                 1                       1                      2                 Paternity           2                 1                    1                    2                        0 
3                 1                       1                      3                  whatever1         null             null                 null                 null                      null 
4                 1                       1                      4                  whatever2         null             null                 null                 null                      null 
5                 1                       1                      5                  whatever3         null             null                 null                 nu开发者_如何学编程ll                      null 

I couldn't put this together. How can I do this?

Thanks.

That's what LEFT JOIN is for, instead of INNER JOIN

SELECT e.*,w.* 
FROM episodes e 
LEFT JOIN watchlist w 
   ON e.episode_id=w.watchlist_series_episode_id AND w.watchlist_user_id='1'
WHERE e.episode_series_id='1' 
AND e.episode_series_season='1' 

• The condition on w.watchlist_user_id must also appear in the ON clause.

Just use LEFT JOIN?

SELECT * FROM episodes e 
LEFT JOIN watchlist w ON e.episode_id = w.watchlist_series_episode_id 
WHERE e.episode_series_id='1' 
AND e.episode_series_season='1' 
AND w.watchlist_user_id='1'

Change your INNER JOIN to an OUTER JOIN or LEFT JOIN (which is LEFT OUTER JOIN).

Try having left join episode on the left and watchlist on the right .... It will return all the episodes and corresponding watch - list if any ...