I seem to be unable to get a good OUTER JOIN with these two Plex database tables.
I want to get a list of all TV show episodes (miv.library_section_id = 2) on my server and when they were added (mi.added_at), including ones that I've watched (miv.account = 1) and when I watched them (miv.viewed_at), where applicable.
I expect to get thousands of episodes, all with added_at filled in with the date they were added to Plex, and with most of them showing NULL for the viewed_at column because I haven't watched them yet.
Instead, I am not getting any NULL results at all, and only a few hundred results from the below query, leading me to believe that it's still performing an INNER JOIN, regardless of the LEFT OUTER JOIN clause that I specified in the query.
SELECT mi.added_at,
miv.viewed_at,
miv.title,
mi.year,
miv.parent_title,
miv.grandparent_title
FROM metadata_items mi
LEFT OUTER JOIN metadata_item_views miv USING (guid)
WHERE miv.account_id = 1 AND miv.library_section_id = 2
What am I doing wrong and what can I do to get the results I expect?
