How can I combine these tables to get the correct output?-CodePudding

I'm starting to learn sql queries and trying to figure out some more complex ones (for me). As I have these tables and schemas:

Customer (CustomerID, name, address, age, balance)

Director (DirectorID, DirectorName, AwardWinner)

Movies (MovieID, title, DirectorID, rating, productionStudio, format, ProductionYear)

            DirectorID FK references director

Rented (CustomerID, movieID, Pickupdate, returnDate)

            CustomerID FK references Customer

            movieID FK references Movies

In Movies schema

      format could be ‘VHS’, ‘DVD’, ‘Blue Ray’.

      rating in movies could have values such as ‘PG’, ‘PG13’,’ R’… etc

      ProductionStudio could have values such as ‘Universal Studio’, ‘Disney’ …etc.

In Director schema

       awardWining has a value of 1 if the director won an award otherwise it will be 0.

I'm trying to figure out how to join them in order to figure out who rented more than 3 titles?

CodePudding user response：

You can use aggregation with group by and having clause

select c.CustomerID, c.name,count(title)
from Customer c inner join Rented r 
                on c.CustomerID=r.CustomerID
     inner join Movies m
                on r.movieID=m.movieID
group by c.CustomerID, c.name
having count(title)>=3

CodePudding user response：

use aggregation and join

 SELECT r.CustomerId FROM Rented r
join Customer c on c.CustomerId=r.CustomerId
join Movies m on r.movieID= r.movieID
 GROUP BY r.CustomerId 
HAVING COUNT(DISTINCT m.title) > 3