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

Databases

Gregory Ledger
Gregory Ledger
6,116 Points
Posted by Gregory Ledger
Gregory Ledger
6,116 Points

Using index ? table in SQL query

Part way through Reporting with SQL but I don't think the answer to this is in the course.

I have three tables in a database. Say the first is an actors table with an id column and a name column. The second is a a movies table with an id column and a name column. The third is a table with an id column, an actor_id column, and a movie_id column. The actor_id column corresponds to the id column in the actor table. The movie_id column corresponds to the id column in the movies table.

I think this third table is either a lookup table or an index table, but I'm not really sure, googling returns very difficult pages that don't really answer the question.

Now I need a select statement that will return the actor name and a corresponding movie name ( a movie the actor appeared in).

What SQL course do I need to cover this, as I am in a bit of a hurry to solve this.

3 Answers

miikis
miikis
44,957 Points

Hey Gregory,

You're describing a many-to-many relationship between actors and movies. In cases such as this, you would need a join table (or a linking table ... or a junction table) to model a relationship between the foreign keys on each side of the association. Check out this link for more.

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

:point_right: The third table is a junction table.

It's essential for establishing many to many relationships. I would expect these would have been covered earlier in this course or the prerequisite. If not, you may want to report this as a bug on the Support page.

I do know that this topic is covered in the Querying Relational Databases course, specifically in the video on Many to Many Relationships.

Gregory Ledger
Gregory Ledger
6,116 Points
Gregory Ledger
Gregory Ledger
6,116 Points

Thank you both. Both answers are best. No way to choose that.