CODEWITHSUNDEEP
mysqlsqljoin
Arda
Arda

Reputation: 6916

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                 null                      null

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

Thanks.

Upvotes: 1

Views: 4509

Answers (4)

Nitin Midha
Nitin Midha

Reputation: 2268

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 ...

Upvotes: 0

Ken White
Ken White

Reputation: 125679

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

Upvotes: 0

anothershrubery
anothershrubery

Reputation: 21003

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'

Upvotes: 0

Galz
Galz

Reputation: 6832

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.

Upvotes: 5

Related Questions