CODEWITHSUNDEEP
mysqlsql
rapidash
rapidash

Reputation: 278

SQL query, three tables

So let's same I'm trying to find actors who are in two movies together (for the purpose of a degrees of separation page). I have databases as such (this is just some made up data):

actors 

id     first_name     last_name    gender
17     brad           pitt         m
2      kevin          bacon        m

movies

id     name               year
20     benjamin button    2008

roles 

a_id    m_id     role
17      20       Mr. Benjamin Button

So I want to return the names of the movies which both actors are in. I have the first and last names of two actors.

I'm having a lot of trouble getting this to work. What I'm having trouble with, specifically, is the SELECT part 

SELECT name FROM movies JOIN . . .

I'm starting with first_name and last_name values for each

Upvotes: 1

Views: 197

Answers (3)

Steve Mayne
Steve Mayne

Reputation: 22818

You must join twice:

SELECT m.name movie_name
FROM movies m join roles r1 on
   r1.m_id = m.id join actors a1 on
   r1.a_id = a1.id join roles r2 on
   r2.m_id = m.id join actors a2 on
   r2.a_id = a2.id
WHERE
  a1.first_name = 'brad' and a1.last_name = 'pitt' and
  a2.first_name = 'kevin' and a2.last_name = 'bacon'

Show all actor combinations per film:

SELECT m.name movie_name, a1.id actor1, a2.id actor2
FROM movies m join roles r1 on
   r1.m_id = m.id join actors a1 on
   r1.a_id = a1.id join roles r2 on
   r2.m_id = m.id join actors a2 on
   r2.a_id = a2.id
WHERE
  a1.id < a2.id

The < ensures that each combination is only reported once.

Upvotes: 4

Nicola Cossu
Nicola Cossu

Reputation: 56357

select m.name,group_concat(concat_ws(' ',a.first_name,a.last_name) order by a.last_name) as actors
from actors as a
inner join roles as r on a.id = r.a_id
inner join movies as m on m.id = r.m_id
where r.a_id in (2,17)
group by r.m_id 
having count(r.a_id) = 2
order by m.name

Upvotes: 1

IUnknown
IUnknown

Reputation: 22448

declare @FirstActorID int, @SecondActorID int;

select m.[name] from movies m inner join [roles] r1 on r1.m_id = m.id and r1.a_id = @FirstActorID inner join [roles] r2 on r2.m_id = m.id and r2.a_id = @SecondActorID

Upvotes: 0

Related Questions