CODEWITHSUNDEEP
mysqlsql
Yongan Wu
Yongan Wu

Reputation: 133

How do I do select specific rows in a table based on specific conditions in the table?

I have sat all day thinking about this problem and I can't seem to come up with a solution. Lets make an example, I have a table that looks like this: 

| movies | personname  | personrole |  
-------------------------------------
| A      | p1          | director   |  
| A      | p1          | producer   |  
| A      | p1          | writer     |
| A      | p1          | actor      |
| A      | p2          | actor      |  
| A      | p3          | writer     |
| A      | p3          | producer   |
| B      | p4          | director   |
| B      | p4          | producer   |
| B      | p4          | writer     |

Now, what I want to do is to list all people in this table that was a director, producer, and a writer for a movie. Any help is appreciated!

This as output: 

| movies | personname | 
-----------------------
| A      | p1         | 
| B      | p4         |

Upvotes: 1

Views: 54

Answers (1)

John Woo
John Woo

Reputation: 263683

This problem is called SQL of Relational Division.

SELECT  movies,
        personName
FROM    tableName
WHERE   personrole IN ('director','producer','writer')
GROUP   BY movies, personName
HAVING  COUNT(*) = 3

if personrole is not unique for every movies, and personname, you need to count unique values using DISTINCT

SELECT  movies,
        personName
FROM    tableName
WHERE   personrole IN ('director','producer','writer')
GROUP   BY movies, personName
HAVING  COUNT(DISTINCT personrole) = 3

Upvotes: 2

Related Questions