SQL query showing element more than once.
- by Sorin Cioban
Hi guys,
I have the following SQL query but it doesn't entirely work because it shows each element more than once.
select c.name, c.director, c.title, count(c.title) as numfilms
from casting c
join casting c1 on c1.name = c.name
Group by c.name, c.director, c.title
Having count (c.title) > 1
order by numfilms
So what it does it shows which actors have made more than 1 film. But unfortunately, this query shows each actor as many times as the number of movies he made. Using distinct doesn't work as actor may have been in different movie with same title and different director.
