CODEWITHSUNDEEP
sqloracle-databasejoin
rfgamaral
rfgamaral

Reputation: 16842

How should I join these 3 SQL queries in Oracle?

I have these 3 queries:

SELECT
  title, year, MovieGenres(m.mid) genres,
  MovieDirectors(m.mid) directors, MovieWriters(m.mid) writers,
  synopsis, poster_url
FROM movies m
WHERE m.mid = 1;

SELECT AVG(rating) FROM movie_ratings WHERE mid = 1;

SELECT COUNT(rating) FROM movie_ratings WHERE mid = 1;

And I need to join them into a single query. I was able to do it like this:

SELECT
  title, year, MovieGenres(m.mid) genres,
  MovieDirectors(m.mid) directors, MovieWriters(m.mid) writers,
  synopsis, poster_url, AVG(rating) average, COUNT(rating) count
FROM movies m INNER JOIN movie_ratings mr
  ON m.mid = mr.mid
WHERE m.mid = 1
GROUP BY
  title, year, MovieGenres(m.mid), MovieDirectors(m.mid),
  MovieWriters(m.mid), synopsis, poster_url;

But I don't really like that "huge" GROUP BY, is there a simpler way to do it?

Upvotes: 2

Views: 477

Answers (3)

Jim Hudson
Jim Hudson

Reputation: 8079

I guess I don't see the problem with having several GroupBy columns. That's a very common pattern in SQL. Of course, code clarity is often in the eye of the beholder.

Check the explain plans for the two approaches; my guess is you'll get better performance with your original version since it only needs to process the movie_ratings table once. But I haven't checked, and that will be somewhat data and installation dependent.

Upvotes: 2

stjohnroe
stjohnroe

Reputation: 3206

how about

SELECT
  title, year, MovieGenres(m.mid) genres,
  MovieDirectors(m.mid) directors, MovieWriters(m.mid) writers,
  synopsis, poster_url,
  (SELECT AVG(rating) FROM movie_ratings WHERE mid = 1) av,
  (SELECT COUNT(rating) FROM movie_ratings WHERE mid = 1) cnt 
FROM movies m
WHERE m.mid = 1;

or

SELECT
  title, year, MovieGenres(m.mid) genres,
  MovieDirectors(m.mid) directors, MovieWriters(m.mid) writers,
  synopsis, poster_url,
  av.av,
  cnt.cnt 
FROM movies m,
  (SELECT AVG(rating) av FROM movie_ratings WHERE mid = 1) av,
  (SELECT COUNT(rating) cnt FROM movie_ratings WHERE mid = 1) cnt 
WHERE m.mid = 1;

Upvotes: 0

Ronnis
Ronnis

Reputation: 12843

You could do something like this:

SELECT title
      ,year
      ,MovieGenres(m.mid) genres
      ,MovieDirectors(m.mid) directors
      ,MovieWriters(m.mid) writers
      ,synopsis
      ,poster_url
      ,(select avg(mr.rating) 
         from movie_ratings mr 
        where mr.mid = m.mid) as avg_rating
      ,(select count(rating)  
         from movie_ratings mr 
        where mr.mid = m.mid) as num_ratings
  FROM movies m
 WHERE m.mid = 1;

or even

with grouped as(
   select avg(rating)   as avg_rating 
         ,count(rating) as num_ratings
     from movie_ratings 
    where mid = 1
)
select title
      ,year
      ,MovieGenres(m.mid) genres
      ,MovieDirectors(m.mid) directors
      ,MovieWriters(m.mid) writers
      ,synopsis
      ,poster_url
      ,avg_rating
      ,num_ratings
  from movies m cross join grouped
 where m.mid = 1;

Upvotes: 5

Related Questions