Strahan Posted March 15, 2017 Share Posted March 15, 2017 (edited) Hello. I have two tables and I want to get data back from both in one query. Table 1: media Fields fileid (int, index), parent (int - match to info 'id' field), filename (varchar[30]), filesize (bigint) Table 2: watchlog Fields wid (int, index), parent (int - match to media 'fileid' field), watched (datetime), user (int) So when I do a select to get all the files in a parent folder, I'd like to show if they were watched or not and have a mouseover to show when. Problem is, sometimes things are rewatched and there are multiple entries with the same parent in watchlog. If I do: SELECT m.fileid,m.filename,w.watched FROM media m LEFT OUTER JOIN watchlog w ON m.fileid = w.parent WHERE m.parent = 6816 ORDER BY filename I get all my files so I can list them, but ones with multiple watches are listed multiple times. I'd like it to return just one watchlog record per each media record. It should choose the latest "watched" value when it picks the one record to return. How do I do that? Oh, and I want the watchlog to only return data for the user in question. I tried the WHERE clause "m,parent = 6816 AND w.user = 1" but then it only returned the two records in media out of 24 that had been watched. ------------ Oops, nevermind. I posted this then a minute later figured out a way, lol. Is this correct: SELECT m.fileid,m.filename,(SELECT datewatched FROM watchlog w WHERE parent = m.fileid AND w.user = 1 ORDER BY datewatched DESC LIMIT 1) FROM media m WHERE m.parent = 6816 ORDER BY filename I mean, it works, but is that the "right" way to approach it? Want to be sure I'm keeping it efficient as possible. Thanks. Edited March 15, 2017 by Strahan Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 16, 2017 Share Posted March 16, 2017 Use MAX() to get the last date alone with a GROUP BY clause SELECT m.fileid, m.filename, MAX(w.watched) as last_watched FROM media m LEFT OUTER JOIN watchlog w ON m.fileid = w.parent WHERE m.parent = 6816 GROUP BY m.fileid ORDER BY filename Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.