m_t1990 - 5 months ago 9

SQL Question

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
)
```

Source (Stackoverflow)

Comments