Description of the BSD.
Sucursales
+-----+-----+-----+-
| idSuc | Direccion|
====================
Peliculas
+-----+-----+-------+
| idPeli| NombrePeli|
=====================
Inventario
+-----+-----+-------+
| idPeli| idSuc |
=====================
I am asked to list the branches in which they have exactly the same inventory ie the same movies available in the form of asymmetric pairs
Example: if in the inventory we have
Inventario
+-----+-----+-------+
| idPeli| idSuc |
=====================
| 1 | 0 |
=====================
| 2 | 0 |
=====================
| 1 | 1 |
=====================
Tendria que retornar
+-----+-----+-------+
| Suc1 | Suc2 |
=====================
| 0 | 1 |
=====================
Because branch 0 has the same films recorded in the inventory table as branch 1.
The problem is that I have to do this only with subqueries within the where I can not do a "From Select", and when I compare I hardly find a row that meets the requirements, obviously it stays with that couple and the idea is that only show the branches when they have all the same pairs of movies.
ConsultSql obtained so far
select DISTINCT inv.idSucursal,inv2.idSucursal
FROM inventario as inv JOIN inventario as inv2 on inv.idSucursal<inv2.idSucursal
WHERE inv.idPelicula=inv2.idPelicula
and EXISTS(SELECT DISTINCT invc.idPelicula FROM inventario as invc WHERE invc.idSucursal=inv2.idSucursal and invc.idPelicula=inv.idPelicula)
and EXISTS(SELECT DISTINCT invcc.idPelicula FROM inventario as invcc WHERE invcc.idSucursal=inv.idSucursal and invcc.idPelicula=inv.idPelicula)
and EXISTS(SELECT DISTINCT invc2.idPelicula FROM inventario as invc2 WHERE invc2.idSucursal=inv.idSucursal and invc2.idPelicula=inv2.idPelicula)
and EXISTS(SELECT DISTINCT invcc2.idPelicula FROM inventario as invcc2 WHERE invcc2.idSucursal=inv2.idSucursal and invcc2.idPelicula=inv2.idPelicula)
GROUP By(inv.idSucursal)
Check that I know it's working well but it has a From Select, so it does not work for me.
SELECT inv1.idSucursal
, inv2.idSucursal
FROM ( SELECT idSucursal
, GROUP_CONCAT(idPelicula ORDER BY idPelicula) AS peliculas
FROM inventario
GROUP
BY idSucursal ) AS inv1
INNER JOIN ( SELECT idSucursal
, GROUP_CONCAT(idPelicula ORDER BY idPelicula) AS peliculas
FROM inventario
GROUP
BY idSucursal ) AS inv2
ON inv2.peliculas = inv1.peliculas
AND inv1.idSucursal < inv2.idSucursal
I've tried a lot of consultations and ways to do it but I can not get the result.