CODEWITHSUNDEEP
sqldatabaseoracle-databasedatabase-designrelational-database
Jules Mena
Jules Mena

Reputation: 67

How can I rewrite a SQL query without any sub-queries?

How can I rewrite this query without using any subqueries in SQL? I'm not too familiar with how to do this, but I think it's done by using "join."

SELECT title
FROM Movies Old
Where year < ANY
  (SELECT year
  FROM Movies
  WHERE title = Old. title
  );

(Note: this comes from the relation Movies(title, year, length, genre, studioName, producerC#))

Upvotes: 0

Views: 161

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

To literally rewrite your current query using joins you can try this:

SELECT m1.title
FROM Movies m1
INNER JOIN Movies m2
    ON m1.title = m2.title AND
       m1.year < m2.year

But if all you really want is to find movie titles which appear more than once, then when not just use a GROUP BY query:

SELECT title
FROM Movies
GROUP BY title
HAVING COUNT(*) > 1

Upvotes: 2

Related Questions