CODEWITHSUNDEEP
mysql
Nate
Nate

Reputation: 28384

How to select rows based on a column value I don't have, but that exists in the same row as a column value I do have?

Suppose I have a table such as the following:

movie_author | movie_name | movie_price

If I have the movie_name column value for a row, how would I make a query to get all movies with the same movie_author as the one in the row that contains the movie_name I have?

I could do this in two queries: the first getting the movie_author from the row that corresponds to the movie_name I have, and then the second getting all rows with the same movie_author, but how do I do this in one query?

Upvotes: 1

Views: 80

Answers (2)

gaborsch
gaborsch

Reputation: 15758

For example, with IN:

SELECT * from movies m2 
WHERE m2.movie_author in (
    SELECT m1.movie_author 
    FROM movies m1
    WHERE m1.movie_name like '%AAA%'
)

Upvotes: 2

fthiella
fthiella

Reputation: 49049

You could use a join, like this:

SELECT m1.*
FROM movies m1 inner join movies m2
     on m1.movie_author = m2.movie_author
        and m2.movie_name='Movie name'

Upvotes: 2

Related Questions