I am trying to figure out a way to find shared credits by two people in a movie database, for example:
table: 'credits'
columns: 'id','movie','person'
My other problem is a person might have multiple credits for the same movie, so how do I filter out duplicates? Any help would be appreciated. The following is what I have so far. Am I on the right track?
SELECT DISTINCT movie
FROM credits
WHERE person = 'condition1' OR person = 'condition2'
GROUP BY movie
HAVING COUNT(*)=2
SELECT DISTINCT c1.movie
FROM credits AS c1
JOIN credits AS c2 on (c1.movie = c2.movie)
WHERE c1.person = 'john'
AND c2.person = 'kate'
WHERE conditions to JOIN would be faster, but it's a correct answer, nonetheless.
This should work:
SELECT DISTINCT movie
FROM (
SELECT movie, COUNT(person) AS contributors
FROM credits
WHERE person IN('person1','person2')
GROUP BY movie, person
HAVING contributors>1
) t1
group by movie, person should be group by person then select distinct movie can be just select movie. Of course the count will still make it slow.GROUP BY person would give incorrect results when there's a person that has two contributions to the same movie. It might be slow for a table that has millions of rows, but I never said it was fast and you're just trying to find an excuse to give negative votes for everybody that's trying to help(except for yourself of course ...), which is pure trolling.select distinct cr1.movie
from credits as cr1
join credits as cr2
using (movie)
where cr1.person = {person 1 goes here}
and cr2.person = {person 2 goes here}
You no need this HAVING COUNT(*)=2. It gives you only movies with two matches (one person with two credits or with both condition1 and condition2). More than that - you no need group by at all when you using distinct (in this case).
SELECT DISTINCT movie
FROM credits
WHERE person = 'condition1' OR person = 'condition2'
shared in question (still not good with english)
role- editor, actor, producer etc)