m_t1990 m_t1990 - 5 months ago 9
SQL Question

SQL Query structure example for recursive lookup

I'm struggling a bit with this query. Perhaps someone can give me a hint?

My setup consists of 3 tables (Actors, Movies, and Relations). What I want to do is:


Find all actors whose name starts with an A that play in any movie alongside Person1.


That's how far i got:

SELECT Name
FROM Actors a, Movies m, Relations r
WHERE a.Name='A%'
AND Person1.keyPerson = m.KeyPerson
AND m.KeyMovies ....


An example on how to continue would be nice!

UPADATE - tables look like this:

Actors (actorsID, name)
Movies (moviesID, title)
Relations (actorsID, moviesID)

Answer

There are several ways you can do this - one method you can do is via an IN statement, pulling the movies Person1 has been in, and checking the A% person's movies.

Select  Distinct A.Name
From    Actors      A
Join    Relations   R   On  R.ActorId = A.ActorId
Where   A.Name Like 'A%'
And     R.MovieId In
(
    Select  R2.MovieId
    From    Actors      A2
    Join    Relations   R2  On  R2.ActorId = A2.ActorId
    Where   A2.Name = 'Person1'
)

Another method is via an EXISTS statement, looking for any movies that Person1 has been in that A% has also been in:

Select  A.Name
From    Actors      A
Join    Relations   R   On  R.ActorId = A.ActorId
Where   A.Name Like 'A%'
And Exists
(
    Select  *
    From    Movies      M2
    Join    Relations   R2  On  R2.MovieId = M2.MovieId
    Join    Actors      A2  On  R2.ActorId = R2.ActorId
    Where   A2.Name = 'Person1'
    And     M2.MovieId = R.MovieId
)
Comments