CODEWITHSUNDEEP
sqldatabaseoracle-database
Mike Christensen
Mike Christensen

Reputation: 91580

SQL query to find rows with at least one of the specified values

Suppose you had two tables. One called MOVIES:

Then another called ACTORS that contains people who appear in those movies:

Now, I want to write a query that returns any movie that contains ONE OR MORE of the following actors: "Tom Hanks", "Russell Crowe" or "Arnold Schwarzenegger".

One way to do it would be something like:

SELECT DISTINCT A.MovieId, M.MovieName FROM ACTORS A
INNER JOIN MOVIES M USING (MovieId)
WHERE A.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger');

Which is perfectly fine, however in my case I might have several more of these conditions on the WHERE clause so I want to find a way to make the MOVIES table the primary table I select from.

What's the best way to query for this? I'm using Oracle 11g if that matters, but I'm hoping for a standard SQL method.

Upvotes: 3

Views: 21997

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You can use EXISTS or IN subqueries:

SELECT *
FROM MOVIES m
WHERE EXISTS
(
    SELECT *
    FROM ACTORS a
    WHERE a.MovieId = m.MovieId
    AND a.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger')
)

or

SELECT *
FROM MOVIES m
WHERE m.MovieId IN 
(
    SELECT a.MovieId
    FROM ACTORS a
    WHERE a.ActorName IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger')
)

Upvotes: 13

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

First you should have a 3rd table implementing the n:m relationship:

CREATE TABLE movie (
 movie_id int primary key
,moviename text
-- more fields
);

CREATE TABLE actor (
 actor_id int primary key
,actorname text
-- more fields
);

CREATE TABLE movieactor (
 movie_id int references movie(movie_id)
,actor_id int references actor(actor_id)
,CONSTRAINT movieactor_pkey PRIMARY KEY (movie_id, actor_id)
);

Then you select like this:

SELECT DISTINCT m.movie_id, m.moviename
  FROM movie m 
  JOIN movieactor ma USING (movie_id)
  JOIN actor a USING (actor_id)
 WHERE a.actorname IN ('Tom Hanks', 'Russell Crowe', 'Arnold Schwarzenegger');

Note, that text literals are enclose in single quotes!

Upvotes: 2

Related Questions