Myujth

So, I have an intermediate table in MySQL called "Inventory", with two PKs: idMovie and idSubsidiary. That table looks like this:

----------------------------------
idMovie (int) | idSubsidiary (int)
----------------------------------
0 | 0
2 | 0
1 | 1
3 | 2
----------------------------------

I want to select the IDs of the pair of subsidiaries that have exactly the same movies.

For that, I was thinking about something like this:

select distinct inv1.idSubsidiary, inv2.idSubsidiary
from inventory inv1
join inventory inv2
on inv1.idSubsidiary <> inv2.idSubsidiary
where not exists (

 SELECT i1.idSubsidiary, i1.idMovie , i2.idSubsidiary, i2.idMovie 
 FROM inventory i1
 INNER JOIN inventory i2 ON i1.idMovie = i2.idMovie 
 WHERE (i1.idSubsidiary= inv1.idSubsidiary and i2.idSubsidiary= inv2.idSubsidiary
 AND i2.idSubsidiary IS NULL

)

The result I'm looking for would be something like this:

idSubsidiary | idSubsidiary
---------------------------
0 | 1
3 | 4

So, subsidiary 0 and 1 have the same identical movies on the inventory, same with 3 and 4.

However, the previously shown query is not working. Basically, the query looks up for couples of SubsidiaryID's on Inventory and then runs a nested query to find if the first Subsidiary have any movie that the second Subsidiary does not have. If they don't, it selects both.

However, the nested query is not working. As I said, I want to do a left join of the same table without the inner part.

Any help is much appreciated :)

The simplest method in MySQL is to do double aggregation:

select movies, group_concat(idSubsidiary) as subsidiaries
from (select i.idSubsidiary, group_concat(idMovie order by idMovie) as movies
 from inventory i
 group by i.idSubsidiary
 ) s
group by movies
having count(*) > 1;

Each row in the result set is a set of movies with the set of ids that have exactly those movies. Those are the duplicates.