Need help on a sql-query
#1
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
Reply
#2
Now I've the query to find all tvshows seasons and the number of episode per season. But now the problem is: how can I get only the last watched episode per show?

Code:
SELECT ts.c00, e.c12, e.c13 as int FROM tvshow ts, tvshowlinkepisode te, episode e WHERE ts.idShow = te.idShow AND te.idEpisode = e.idEpisode GROUP BY ts.idShow, ts.C00, e.c12;

Code:
...
Heroes S 2-E 6
Heroes S 3-E 2
How I Met Your Mother S 1-E 22
How I Met Your Mother S 2-E 22
How I Met Your Mother S 3-E 20
How I Met Your Mother S 4-E 22
How I Met Your Mother S 5-E 11
Journeyman S 1-E 1
Las Vegas S 1-E 23
Leverage S 1-E 1
Life on Mars (US) S 1-E 1
Life S 1-E 11
Life S 2-E 21
My Name Is Earl S 1-E 23
My Name Is Earl S 2-E 23
My Name Is Earl S 3-E 1
My Name Is Earl S 4-E 27
...
Thanks

kimx
Reply
#3
the info is stored in the files table. files.lastPlayed. so join on episodelinkfile and order on that field.
Reply
#4
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
Reply
#5
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
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply
#6
Thanks, I'll give it a try.

kimx
Reply

Logout Mark Read Team Forum Stats Members Help
Need help on a sql-query0