Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Jonathan Mecham
Jonathan Mecham
1,100 Points
Posted by Jonathan Mecham
Jonathan Mecham
1,100 Points

SQL for multiple values in a cell

Just wrapping up the database deep dive, and as I think about what I want to do with the database I'm creating, I'm wondering what methods there are for dealing with multiple pieces of data for one cell.

For example, going on the movies database example, what if I wanted to associate a title with MULTIPLE genres? In the movies table, genre column, I'd want to be able to have more than one value. Does that make sense?

I think I'm missing something conceptually here that would make this very simple, but don't know where to look, and every schema I think up involves a lot of redundancy.

3 Answers

Chad Harris
Chad Harris
2,892 Points

You need a "junction table" that associates the multiple genres per single movie. This table is separate from your 'movies' and 'genre' table and has a many-to-many relationship between the two tables.

Let me see if I can better explain it by diagram:

tbl_movies
*---------*----------*
| id (PK) | title    |
*---------*----------*
|      .. | ..       | 
|      23 | Jaws     |
|      24 | Alien    |
*---------*----------*

tbl_genre
*---------*----------*
| id (PK) | genre    |
*---------*----------*
|       1 | Comedy   |
|       2 | Action   |
|       3 | Horror   |
*---------*----------*

tbl_movie_to_genre
*---------------*---------------*
| movie_id (FK) | genre_id (FK) |
*---------------*---------------*
|            23 |             2 |
|            24 |             2 |
|            24 |             3 |
*---------------*---------------*

Where PK = Primary Key and FK = Foreign Key obviously.

Now if you wanted to get all the genres for a particular movie (ex. Alien), you could use the following SQL statement:

SELECT g.genre AS genres FROM tbl_genre AS g, tbl_movie_to_genre AS mtg
WHERE mtg.movie_id = 24 AND g.id = mtg.genre_id;

Result:

*--------*
| genres |
*--------*
| Action |
| Horror |
*--------*
James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

For example, going on the movies database example, what if I wanted to associate a title with MULTIPLE genres?

You need a genre table for that. The short answer is that queries wouldn't return correct information if you had a field with multiple pieces of data.

every schema I think up involves a lot of redundancy.

The key thing to remember is that every piece of information must have one source. So you never need to update say a movie name in 2 places.

Jonathan Mecham
Jonathan Mecham
1,100 Points
Jonathan Mecham
Jonathan Mecham
1,100 Points

That makes sense, but even with a genres table, is there a way to associate more than one element in the genre table with a single element of the movies table?

James Barnett
James Barnett
39,199 Points
James Barnett
James Barnett
39,199 Points

You create 2 tables, associate them via an id, and for genres make one entry per genre.

movies
--------
id | title
1  | jaws
2  | alien

genre
-------
id | genre
1  | action
2  | action
2  | horror
Jonathan Mecham
Jonathan Mecham
1,100 Points
Jonathan Mecham
Jonathan Mecham
1,100 Points

Chad, those diagrams were money! Thanks for the response. I was losing motivation fast to learn about databases feeling like there should be a more simple solution to the question. I'm feeling good about pushing farther learning about this stuff between your answer and the new PHP and MySQL course Treebook launched, that looks like it covers many-to-many relationships. Thanks again.

Chad Harris
Chad Harris
2,892 Points

I completely understand. Databases design was something that I learned over time and I'm still learning. Sometimes I look at my old projects and wonder what I was thinking when I created the tables for an app but like anything else, you'll get better with time/experience! Glad I could help.

And yes, the PHP courses look cool! Glad they finally released something on PHP because I don't care for Ruby too much.