I need to extract the pairs of storeID that contain the exact same movies, using this table as an small example
| storeID | movieID |
|---|---|
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | b |
| 2 | c |
| 3 | d |
The result query should be storeID 1 and storeID 2 as they share the same movies.
2 Answers
WITH cte AS ( SELECT storeID, GROUP_CONCAT(movieID ORDER BY movieID) movies
FROM table
GROUP BY storeID )
SELECT GROUP_CONCAT(storeID)
FROM cte
GROUP BY movies
HAVING COUNT(*) > 1
This query:
SELECT storeID, GROUP_CONCAT(movieID ORDER BY movieID) movies
FROM tablename
GROUP BY storeID
returns all the movies of each store as a comma separated list.
Use it as a CTE and with a self join get the pairs of stores with the same movies:
WITH cte AS (
SELECT storeID, GROUP_CONCAT(movieID ORDER BY movieID) movies
FROM tablename
GROUP BY storeID
)
SELECT c1.storeID storeID1, c2.storeID storeID2
FROM cte c1 INNER JOIN cte c2
ON c1.movies = c2.movies AND c1.storeID < c2.storeID
Related