CODEWITHSUNDEEP
mysql
LeoVannini
LeoVannini

Reputation: 95

Specific query between multiple tables using foreign keys

I'm working on a database project for school and I'm relatively new at SQL queries in a class so I was looking for help on a specific query.

Here's my schema :

movies
----------------------------
id:integer (primary key)
title: varchar(100) 
--------------------------

stars
--------------------------
id:integer (primary key)
--------------------------

stars_in_movies
---------------------------------------
star_id:integer, referencing stars.id
movie_id:integer, referencing movies.id
-----------------------------------------

So, provided a star's ID, what would be the query to get title of all the movies a star was in?

I know that this is a pretty specific question for a query, but I was wondering if someone could provide a specific query and an explanation as to why the query does what it does, as I'm more interested in the "why the query works" aspect of the answer.

Thanks again for all the help.

Upvotes: 0

Views: 25

Answers (2)

starko
starko

Reputation: 1149

select m.title from
stars_in_movies sim join movies m
on m.id=sim.movie_id
where sim.star_id = 1

Connect this table give you title of film where star play. Check link: SQL Fiddle

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to join the table as

select
m.id,
m.title
from movies m
join stars_in_movies sm on sm.movie_id = m.id
join stars s on s.id = sm.star_id
where s.id = {your star id to be searched}

Upvotes: 1

Related Questions