I have three mysql tables with a connecting unique id in each
123 Test
456 Another Test
123 1 16
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
123 Test 1 NULL
456 Another… NULL 1
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments