SQL query using two tables plus a third for linking them

stereo_steve

Hi Guys,

Sorry if this the wrong place for SQL questions. My xbox downloads tv shows automatically at night using an RSS feed. It then scrapes thetvdb.com to get information about the show and enters this information into a sqlite db.

I am going to create a script that emails me in work informing me what was downloaded the previous night including a synopsis of the episode. I then know what I will be watching in the evening.

The problem is pulling the correct information out of the db to email to myself.

The three tables are:

http://wiki.xbmc.org/index.php?title=The_XBMC_Database#episode
http://wiki.xbmc.org/index.php?title=The_XBMC_Database#tvshow
http://wiki.xbmc.org/index.php?title=The_XBMC_Database#tvshowlinkepisode

The last table links the primary keys of the first two db's together.

How do I go about this? I think it should be like ...

select episode.c00, episode.c01 , episode.c05, c00.tvshow
join tvshowlinkepisode.idShow tvshowlinkepisode.idEpisode
where episode.c05='some date'

Any help pointing me in the correct direction is very much appreciated.

The Gibmiester

I have no way of testing this but try

SELECT episode.coo, episode.co1, episode.co5, tvshow.c05 
FROM episode, tvshow, tvshowlinkepisode
WHERE episode.idEpisode = tvshowlinkepisode.idEpisode
AND tvshow.idshow = tvshowlinkepisode.idshow

You can then add another AND for episode.c05='some date'

stereo_steve

Brilliant Gibmeister, thats alot. It worked straight away.

The bit linking the two primary keys was confusing me. It's nice to have a working example.

One last question, for the line ...

AND episode.c05='some date'

Is it possible to input some kind of date variable so that yesterday will be queried? In the format yyyy-mm-dd. If not I'll work around it with the script.

Thanks again

Giblet

Try

SELECT date('now','-1 day');

Can be used like

where date > date('now','-1 day');

stereo_steve

Thanks Guys, Its working perfectly now. I learnt some SQL in the process