Join two tables, but only get single results from other table

[Strahan]

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

[Psycho]

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