Jump to content

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

Recommended Posts

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 by Strahan
Link to post
Share on other sites

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
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.