How to perform this task in 1 single, efficient mysql query?

Question

I have a list of TV shows stored in 1 table. Another table stores show genres (action, romance, comedy).

Most shows usually have more than 1 genre, so having a single tv_genre column and putting the genre ID in there isn't an option.

I could create a look up table which would store tv show id + genre id, and I could insert 1 row for every genre associated with the show.

Where things get fuzzy for me is when I want to output a list of shows on the index, and genre names associated with the tv show. How would I tie the 3 tables together into 1 efficient query (instead of running a separate query for each item on index, getting its genres).

For the purposes of this post, the tables are as follows

TV Show Table
- tv_id
- tv_name

Genre Table
- genre_id
- genre_name

Thanks!

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/join.html

JOIN is the heart of SQL. you'll wind up with something like

select tv_name, genre_name
from tvshows
   left join shows_genres on tvshows.tv_id = shows_genres.tv_id
   left join genres on genres.genre_id = shows_genres.genre_id

Answer 2

You can use MySQL's GROUP_CONCAT function:

SELECT
    t.tv_name, GROUP_CONCAT(g.genre_name)
  FROM
    tv_shows t
    LEFT JOIN show_genres sg ON (t.tv_id = sg.tv_id)
    LEFT JOIN genres g ON (sg.genre_id = g.genre_id)
  WHERE
    /* whatever */
  GROUP BY t.tv_name

Your index page won't be particularly efficient; it can't be: you're pulling the entire table (well, all three tables). If it ever becomes a problem, look into caching.

Answer 3

Show-Genre Table - rv_id int - genre_id int

 select tv_show.name, genre.genre_name from tv_show,genre, show_genre where 
show_genre.tv_id = tv_show.tv_id and genre.genre_id=show_genre.genre_id

Something like this

Answer 4

May not be the best sql but my idea was

select tv_name, genre_name
from tv_table, genre_table, lookup_table
where
    tv_table.tv_name = "superman"
    and
    tv_table.tv_id = lookup_table.tv_id
    and
    lookup_table.genre_id = genre_table.genre_id