CODEWITHSUNDEEP
phpmysqlsqljoin
Justin Erswell
Justin Erswell

Reputation: 708

Three Table Join MySQL

I have three mysql tables with a connecting unique id in each

Episodes

epis_epsiode_id || epis_title

123                Test
456                Another Test

Watch List

wali_pisode_id || wali_status || wali_user_id

123               1              16

Watch Log

wast_episode_id || wast_status || wast_user_id

456                1              16

The above shows two episodes with episode 123 added to user 16 watch list and episode 456 logged a watched by the same user.

What I need to do is build a query which returns all of the the Episodes and where the user_id in both Watch List and Watch Log is = 16 display the status for each table against the episode row or else show null

I can get one table join working but when I add in another well, hence the reason for the question.

I am aware of other questions similar to this but having tried the majority of the answers given I am still struggling to get the desired result.

Could some one very kind an helpful post a query example that would achieve the above?

Thanks

EDIT

SELECT 
    e.*, 
    wl.wali_status,
    ws.wast_status 
FROM 
    meta_episodes e
LEFT OUTER JOIN
    app_watch_list wl
    ON wl.wali_episode_id = e.epis_episode_id
    AND wl.wali_user_id = 16
LEFT OUTER JOIN
    app_watch_status ws
    ON ws.wast_episode_id = e.epis_episode_id
    AND ws.wast_user_id = 16

Desired Results

Results

epis_episode_id || epis_title || wali_status || wast_status

123                Test          1              NULL
456                Another…      NULL           1

Upvotes: 2

Views: 161

Answers (2)

Michal Brašna
Michal Brašna

Reputation: 2323

SELECT e.*, wali_status, wast_status
FROM meta_episodes e
LEFT JOIN app_watch_list wl ON e.epis_epsiode_id = wl.wali_episode_id AND wali_user_id = 16
LEFT JOIN app_watch_status ws ON e.epis_epsiode_id = ws.wast_episode_id AND wast_user_id = 16

More on fiddle http://sqlfiddle.com/#!2/071751/13

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This should work:

SELECT  E.epis_episode_id, 
        E.epis_title,
        WLI.wali_status,
        WLO.wast_status
FROM `Episodes` AS E
LEFT JOIN `Watch List` AS WLI
    ON E.epis_episode_id = WLI.wali_episode_id
    AND WLI.wali_user_id = 16
LEFT JOIN `Watch Log` AS WLO
    ON E.epis_episode_id = WLO.wast_episode_id
    AND WLO.wast_user_id = 16

Upvotes: 2

Related Questions