2010-01-04, 15:37
actually this entire query is not right (the tv episodes one), the joining of the tables is done a wrong order (besides the case sensitivity issue)
the current query is :
note that the 1st join joins tvshow and tvshowlinkepisode before joining tvshowlinkepisode with episode, so i have changed it to (also after upper-casing the lower-cased fields):
all i did is that i moved the:
before
this actually returned something back (of course this is all in the query browser)
the current query is :
Code:
select episode.c12, path.strPath, tvshow.c00, tvshow.c08, count(1), count(files.playCount)
from episode
join tvshow on tvshow.idShow = tvshowlinkepisode.idShow
join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode
join files on files.idFile = episode.idFile
join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow
join path on path.idPath = tvshowlinkpath.idPath
where tvshow.idShow = 16 group by episode.c12
note that the 1st join joins tvshow and tvshowlinkepisode before joining tvshowlinkepisode with episode, so i have changed it to (also after upper-casing the lower-cased fields):
Code:
select episode.c12, path.strPath, tvshow.c00, tvshow.c08, count(1), count(files.playCount)
from episode
join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode
join tvshow on tvshow.idShow = tvshowlinkepisode.idShow
join files on files.idFile = episode.idFile
join tvshowlinkpath on tvshowlinkpath.idShow = tvshow.idShow
join path on path.idPath = tvshowlinkpath.idPath
where tvshow.idShow = 16 group by episode.c12
all i did is that i moved the:
Code:
join tvshowlinkepisode on tvshowlinkepisode.idEpisode = episode.idEpisode
before
Code:
join tvshow on tvshow.idShow = tvshowlinkepisode.idShow
this actually returned something back (of course this is all in the query browser)