Posts: 33
Joined: Feb 2008
Reputation:
0
kimx
Junior Member
Posts: 33
Hi, I want to create a script which gets the last watched episode for every show that I have in my library. I took a look on the wiki and I've found the tables to join to accomplish my needs. But I'm not so good at sql queries.
The tables are tvshow, tvshowlinkepisode and episode. The needed information from the database are tvshow.c00 (Show Title), episode.c00 (Episode Title ), episode.c12 (SeasonNo), episode.c12 (EpisodeNo).
Can someone help me?
Thanks
kimx
Posts: 12,706
Joined: Nov 2003
Reputation:
129
spiff
Team-Kodi Member
Posts: 12,706
the info is stored in the files table. files.lastPlayed. so join on episodelinkfile and order on that field.
Posts: 33
Joined: Feb 2008
Reputation:
0
kimx
Junior Member
Posts: 33
There was as misunderstanding: I need the last episode per show, not based on the last watching time, then it could be that I watch an episode many times.
Lets make an example: in season 3 there are 10 episodes I've seen 4, then I should get s3e4.
Only the biggest number for season and episode based an the watched flag, thats what the query should return for any show.
Thanks
kimx
Posts: 26,215
Joined: Oct 2003
Reputation:
187
So you need to join on the table that has the watched info as cptspiff suggests.
Once you have the files table joined in you can limit the query to those that are watched (files.playCount > 0) and then order by season and then by episode.
Cheers,
Jonathan
Posts: 33
Joined: Feb 2008
Reputation:
0
kimx
Junior Member
Posts: 33
Thanks, I'll give it a try.
kimx