Find the genres that have at least 2 same directors, then print common genres and director id?

Question

The dataset is like this:

g1 = {d1,d3,d5}
g2 = {d1,d3,d2}
g3 = {d4,d3,d5}

I have the following table called director_genre:

      director_id | genre       
       d1           g1
       d1           g2
       d2           g2
       d3           g1
       d3           g2
       d3           g3
       d4           g3
       d5           g1
       d5           g3

The output I want is

      genre |  director_id       
       g1      d1,d3,d5
       g2      d1,d3
       g3      d3,d5

The query I thought of is

SELECT genre
FROM director_genre
GROUP BY genre;

After this, I am not to put the logic as asked in the question. Please help me to get this done.

Are you sure you don't want the first record to say `g1 | d1,d2,d5`? And look into the `GROUP_CONCAT()` function — VvdL, Jun 08 '23 at 11:51
It should be g1 | d1,d3,d5 NOTE: d2 should not be in the list as it is not common for at least 2 same directors — Sahil Kamboj, Jun 08 '23 at 13:54

I J · Accepted Answer · 2023-06-08T16:02:41.207

3

I would suggest the following query:

SELECT d.genre, GROUP_CONCAT(director_id)
FROM director_genre d
WHERE EXISTS (SELECT *
              FROM director_genre d2
              WHERE d.director_id = d2.director_id AND d.genre <> d2.genre
)
GROUP BY d.genre;

The nested subquery will select only records of directors who are associated with at least 2 genres (for each record in d it looks whether there exists a record in d2 with the same director_id but different genre). Then they will be grouped by genre.

edited Jun 08 '23 at 16:02

answered Jun 08 '23 at 12:29

I J

82
6

I have edited the question to correct my output, please have a look and then tell me does your solution works for this output also? – Sahil Kamboj Jun 08 '23 at 13:56
1

I think I get what you need now. For each genre, you want a list of directors who are associated with at least 2 different genres. I've updated the code. – I J Jun 08 '23 at 16:03
1

I think, @I J solved the riddle. – ValNik Jun 08 '23 at 18:47
@IJ thank you It's done, dear. I think you used a correlated subquery to solve it. – Sahil Kamboj Jun 09 '23 at 09:01

score 1 · Answer 2 · answered Jun 08 '23 at 14:01

You want a list of (genre, director) where the director is also associated with another genre. Then group. This could be done like so:

select genre, group_concat(director_id separator ', ') as directors
from dg
where exists (
  select *
  from dg as x
  where x.director_id = dg.director_id
  and x.genre <> dg.genre
)
group by genre

Find the genres that have at least 2 same directors, then print common genres and director id?

2 Answers2