CODEWITHSUNDEEP
mysqlsql
Anon
Anon

Reputation: 71

SQL Retrieving repeated rentals

Question:

write a SQL query that retrieves repeated rentals in which the same customer rents the same movie more than once. if a customer rents the > same movie multiple times, the output should show this (customer's name, movie's title) combination only once

There are 3 tables:

i currently have:

select customer.name, movie.title
from ((rental inner join customer on rental.customer_id = customer.id) inner join movie on rental.movie_id = movie.id)
group by customer.name, movie.title
having count(*) > 1;

but it is incorrect - any thoughts?

Upvotes: 1

Views: 1889

Answers (1)

SatanDmytro
SatanDmytro

Reputation: 537

Rewrite your query as:

SELECT c.name, m.title
FROM rental AS r
  INNER JOIN customer AS c ON r.customer_id = c.id
  INNER JOIN movie AS m ON r.movie_id = m.id
GROUP BY c.name, m.title
HAVING count(*) > 1
;

Your JOIN statements has wrong syntax

EDIT: to avoid naming duplicates change GROUP BY statements:

GROUP BY c.id, m.id

Upvotes: 2

Related Questions