-2

Table layout:

enter image description here

Hi, I would like to retrieve a combination of information from the above tables, primarily a single row of results from the 'episode' table, but also some information from the 'tvshow' table and the 'season' table, that also only applies to that single row I want to retrieve from the 'episode' table, this is my query at the moment:

Select tvshow.series_name, season.season_banner, season.season_poster, episode.season_num, episode.episode_num, episode.episode_name, episode.plot
FROM tvshow,season,episode
WHERE episode.tvshow_id = 1 AND episode.season_num = 1 and episode.episode_num = 1

I'm aware I'd need to use a join, but i'm unsure how to go about doing this, with regards to combining the results from 3 tables.

The closest i've got to returning a single row is this:

SELECT tvshow.series_name, season.season_banner, season.season_poster, episode.season_num, episode.episode_num, episode.episode_name, episode.plot
FROM episode
INNER JOIN tvshow ON episode.tvshow_id = tvshow.tvshow_id
INNER JOIN season ON episode.season_num = season.season_num
WHERE episode.tvshow_id =1
AND episode.season_num =1
AND episode.episode_num =1

That returns the row I want, yet also returns an identical second row aside from the fields that only exist in the season table (season banner & poster) being blank.

John Bergqvist
  • 852
  • 13
  • 38
  • http://stackoverflow.com/questions/8061514/how-to-combine-data-from-multiple-tables-using-sql – Suji Jul 29 '14 at 11:19

2 Answers2

0
   First you need to normalize your data base.
   Your table structure like thata  
        tvshow -->   tv_id as  primary key
        season -->   tv_id as foreign key 
        episode -->  tv_id as foreign key 

   Your join query 

    Select tvshow.series_name, season.season_banner, season.season_poster, episode.season_num, episode.episode_num, episode.episode_name, episode.plot 
    FROM tvshow 
    JOIN season on season.tv_id= tvshow.tv_id
    JOIN episode on episode.tv_id= tvshow.tv_id
    WHERE episode.tv_id = 1
prash.patil
  • 687
  • 8
  • 7
  • That join query is bringing me in results from the tvshows who's id does not match 1. With regards to normalisation, i'd like to have the compound primary key in the seasons table linked as a foreign key to the same values in the episodes table, but i've been told that's not supported yet. – John Bergqvist Jul 29 '14 at 11:04
0

Use Left Join

select ts.series_name,ss.season_banner,es.season_num from tvshow as ts inner join season as ss on ss.tvshow_id=ts.tvshow_id inner join episod as es on ss.season_num=es.season_num where ts.tvshow_id=1 and ss.season_num=2 and es.episode_num=1;
user3852430
  • 1
  • 3
  • that fixes the season problem, but if i set tvshow_id to 2, then it does the same problem as before, but this time with the series_name in the tvshow table. – John Bergqvist Jul 29 '14 at 11:27
  • No, it's still returning values where tvshow_id is 2, season is 1 and episode is 1. – John Bergqvist Jul 29 '14 at 11:35
  • what you want in result? – user3852430 Jul 29 '14 at 11:39
  • I want it so that if there is no value in the episodes table other than the combination of the three values (show id, season num & episode num), it doesn't return anything. – John Bergqvist Jul 29 '14 at 11:40
  • means which field you want in the result? – user3852430 Jul 29 '14 at 11:40
  • everything from episodes table, season banner & season poster from seasons table (where it matches the show id & season num in the episodes table) and the series name from the tvshow table (where it matches the show id from the episodes table). If a user specifies a combination of show id, season num and episode num that does not exist in the episodes table, then the query should not return anything. – John Bergqvist Jul 29 '14 at 11:43
  • You want number of rows or particular single row from query? – user3852430 Jul 29 '14 at 12:00
  • you have to put condition in source code and concat query in the backend source code.like if(episode!=0 && tvshow!=0 && season!=0){} – user3852430 Jul 29 '14 at 12:09
  • here in if condition your query concatinaion is there. – user3852430 Jul 29 '14 at 12:09
  • which language is used in backend? – user3852430 Jul 29 '14 at 12:10
  • I've just tried this: SELECT tvshow.series_name, season.season_banner, season.season_poster, episode . * FROM episode JOIN tvshow ON tvshow.tvshow_id = episode.tvshow_id JOIN season ON season.season_num = episode.season_num WHERE episode.tvshow_id =1 AND season.tvshow_id =1 AND episode.season_num =1 AND episode.episode_num =1 (using season.tvshow_id as the same as episode.tvshow_id and it works fine now. Thanks – John Bergqvist Jul 29 '14 at 12:11
  • ohhh you want tvshow seriesname and season of perticular episode...now i m understand – user3852430 Jul 29 '14 at 12:15