CODEWITHSUNDEEP
mysqlsqldatabaseselect
Big_t boy
Big_t boy

Reputation: 329

Finding actor/movie rows where actor has multiple distinct roles in the same movie

I got 3 tables, actor (id,name), movie (id,name) and casts(aid,mid,role) (aid is the actor id and mid is the movie id). I was trying to get the output like this: if an actor had more than 3 distinct roles in the same movie, print all the combinations, like:

-1.actor.name, movie.name, role1
-2.actor.name, movie.name, role2
-3.actor.name, movie.name, role3

My query is like this:

select a.name, m.name, x.role
from actor a,
     movie m,
     (select distinct role
      from casts c
      where c.aid =a.id and c.mid = m.id
      group by c.aid and c.mid
      having count(distinct role) >=3) as x;

But I got error message:

The multi-part identifier "m.id" could not be bound. The multi-part identifier "a.id" could not be bound.

Please point out where my thought went wrong, I want to be able to do this next time. Thanks.

Upvotes: 2

Views: 1833

Answers (2)

StuartLC
StuartLC

Reputation: 107237

Your initial query is close, but the problem is that you can only return a single column from a subquery, whereas your casts table has a composite key* of two foreign key columns.

Instead, you can do the hard work in a derived table (as you've done in your initial subquery). The benefit of the derived table over the subquery is that you can then join the other tables back to on the two columns to return the friendly column names:

select a.name, m.name, c.`role`
from 
(
    select aid, mid
    from casts
    group by aid, mid
    having count(distinct `role`) >= 3
) x
inner join actor a
    on a.id = x.aid
inner join movie m
    on m.id = x.mid
inner join casts c 
    on x.mid = c.mid and x.aid = c.aid;

* actually, it isn't really a key either, given that the same actor can have multiple roles in the same movie. But we are looking for unique combinations, so its unique after we do the GROUP BY on mid, aid

SqlFiddle here - Duplicate Roles are ignored, and the threshold of 3 roles, same movie is observed.

Upvotes: 2

jarlh
jarlh

Reputation: 44716

Do a join between the 3 tables, and have a sub-select to verify at least 3 different roles:

select a.name, m.name, c.role
from actor a
  join movie m on a.id = m.aid
  join casts c on m.id = c.mid and c.aid = a.id
where a.id in (select aid from casts
               where aid = a.id and mid = m.id
               group by aid, mid
               having count(distinct role) >=3)

Upvotes: 0

Related Questions