首页 文章

加入两个表,但如何显示所有结果?

提问于
浏览
1

我有两个这样的表结构:

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;"

我想加入这两个,并显示所有结果 .

这对我没有帮助:

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'

我正在使用此查询,但像往常一样,它只显示匹配的列(剧集表中的30行,监视列表中的2行)

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'

但它只显示2行(应该如此) .

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

但是我希望显示所有匹配的结果(我想从这两个表中选择所有列,列出剧集,如果有一个来自监视列表的匹配我想要从watchlist列填充数据,否则它们应该为null)

我想得到这样的结果:

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

我不能把它放在一起 . 我怎样才能做到这一点?

谢谢 .

4 回答

  • 0

    那是 LEFT JOIN 的用途,而不是 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'
    
    • w.watchlist_user_id 上的条件也必须出现在ON子句中 .
  • 0

    只需使用 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'
    
  • 5

    INNER JOIN 更改为 OUTER JOINLEFT JOIN (即 LEFT OUTER JOIN ) .

  • 0

    尝试左边的连接剧集和右边的观察列表....它将返回所有剧集和相应的观看列表(如果有的话......)

相关问题