MYSQL compare all the rows of a table by itself

0

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.

    
asked by WhySoBizarreCode 20.11.2018 в 01:34
source

0 answers