CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Aep Nat
Aep Nat

Reputation: 21

SQL 3 tables to join

Table film

film_id title description rental_rate

Table category

category_id name last_update

Table film_category

film_id category_id last_update

I have SQL to select 5 most favorited by rental rate with 

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 5

based of SQL above, how to select 4 most category favorite ?

The result I want is just category.name

Upvotes: 2

Views: 80

Answers (2)

Payer Ahammed
Payer Ahammed

Reputation: 907

Try This:

select * from (select t3.name,sum(t1.rental_rate) as rate from film as t1
inner join film_category as t2 on t1.film_id=t2.film_id
inner join category as t3 on t2.category_id=t3.category_id
group by t2.category_id) as detail order by rate DESC LIMIT 5

Upvotes: 1

Metaphor
Metaphor

Reputation: 374

May be you are looking for this:

SELECT c.name FROM film a
INNER JOIN film_category b ON a.film_id=b.film_id
INNER JOIN category c ON b.category_id=c.category_id
WHERE a.rental_rate IN 
(
    SELECT TOP (5) rental_rate FROM film    
    ORDER BY rental_rate DESC
)

Upvotes: 0

Related Questions