CODEWITHSUNDEEP
sqlsql-server
Kadaj13
Kadaj13

Reputation: 1541

SQL query confusion - actors, roles, movies

I have three tables:

ACTOR

id 
nameofactor

MOVIE 

id
nameOfmovie

CASTS

actorid 
movieid
role

I want to show how the names of actors who had played more than one role in a movie

Here is what I have tried:

select 
    nameOfactor, nameOfmovie, CASTS.role
from 
    ACTOR, MOVIE, CASTS
where 
    ACTOR.id = CASTS.actorid 
    and CASTS.mid = MOVIE.movieid
group by 
    fname, lname, name, role
having 
    count(pid) >= 2;

But it doesn't work.

I guess the problem is in this way I have to put "role" in group by, but as I need to show roles, somehow I have to this

I dont know how to fix this query. I would be happy if anyone can help

Thanks in advance

Upvotes: 0

Views: 846

Answers (3)

StackUser
StackUser

Reputation: 5398

TRY THIS QUERY

--SAMPLE DATA SETUP

DECLARE @Actor TABLE
  (
     id          INT,
     nameofactor VARCHAR(50)
  )

INSERT INTO @Actor
SELECT 1,
       'Act-1'
UNION
SELECT 2,
       'Act-2'
UNION
SELECT 3,
       'Act-3'

DECLARE @MOVIE TABLE
  (
     id          INT,
     nameOfmovie VARCHAR(50)
  )

INSERT INTO @MOVIE
SELECT 1,
       'Mov-1'
UNION
SELECT 2,
       'Mov-2'
UNION
SELECT 3,
       'Mov-3'

DECLARE @CASTS TABLE
  (
     actorid INT,
     movieid INT,
     role    VARCHAR(50)
  )

INSERT INTO @CASTS
SELECT 1,
       1,
       'ROLE-1'
UNION
SELECT 2,
       2,
       'ROLE-2'
UNION
SELECT 1,
       1,
       'ROLE-3'
UNION
SELECT 2,
       2,
       'ROLE-4'
UNION
SELECT 3,
       3,
       'ROLE-5'


--SQL QUERY

SELECT A.*,
       (SELECT NAMEOFMOVIE
        FROM   @MOVIE
        WHERE  ID = B.movieid) NAMEOFMOVIE
FROM   @Actor A
       JOIN (SELECT MOVIEID,
                    ACTORID,
                    COUNT(ROLE) ROLECOUNT
             FROM   @CASTS
             GROUP  BY MOVIEID,
                       actorid
             HAVING COUNT(ROLE) > 1) B
         ON A.id = B.actorid

Upvotes: 0

Dudi Konfino
Dudi Konfino

Reputation: 1136

select nameOfactor, nameOfmovie, count(role)
from ACTOR, MOVIE, CASTS
where ACTOR.id=CASTS.actorid and CASTS.movieid=MOVIE.id  
group by nameOfactor, nameOfmovie
having count(role)>1 ;

Upvotes: 0

DavidG
DavidG

Reputation: 118937

You need to generate the list of actors with multiple roles, then use that to join back to your tables to get the roles.

So first get the list of actors with multiple roles:

SELECT actorid, movieid
FROM CASTS
GROUP BY actorid, movieid
HAVING COUNT(*) > 1

And now you can use that as a subquery and JOIN it back to your other tables:

select nameOfactor, nameOfmovie, CASTS.role
from ACTOR
JOIN CASTS
    ON ACTOR.id=CASTS.actorid
JOIN MOVIE
    ON CASTS.mid=MOVIE.movieid
JOIN (SELECT actorid, movieid
      FROM CASTS
      GROUP BY actorid, movieid
      HAVING COUNT(*) > 1) A MultiRoles
    ON MultiRoles.actorid = CASTS.actorid 
    AND MultiRoles.movieid = CASTS.movieid

Upvotes: 5

Related Questions