MySQL Forums
Forum List  »  Newbie

Limiting results in a joined table
Posted by: Doug Matthews
Date: July 14, 2015 05:58PM

Hi. I have a media site I use to catalog and play my videos. I have a "media" table with all the files and a "watchlog" table to log which I watched. To prevent multiple trips to the DB, I made this my SQL query when I list the files:

SELECT * FROM media m LEFT OUTER JOIN watchlog w ON m.fileid = w.parent WHERE m.parent = {$info["id"]} ORDER BY m.disporder,m.filename

Yes, I know not using parameters is dangerous but this is only accessible via LAN and only used by me :) This works, but the problem is if I watch an episode more than once it gets multiple watchlog entries so I can see all the times I watched it.. but it returns multiple lines then in that query because of it. So in my episode list on the site I may have "Episode 1", "Episode 2", "Episode 2", "Episode 3".

I'm a SQL newb.. I tried doing DISTINCT(filename),* and it yelled at me lol. Can someone tell me how to do it? I don't care about which specific watchlog record it returns, I just want it to return one of the watchlogs for the file in question so I know I watched it at some point.

Thanks!

Options: ReplyQuote


Subject
Written By
Posted
Limiting results in a joined table
July 14, 2015 05:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.