Return individual name pairs that have the same exact elements in a column

Question

I want to find different pairs of names in a table that have the same exact elements in the items column. For instance:

CREATE TABLE t ( name VARCHAR(255), item VARCHAR(255) ); INSERT INTO t VALUES("Alice", "Orange"); INSERT INTO t VALUES("Alice", "Pear"); INSERT INTO t VALUES("Alice", "Lemon"); INSERT INTO t VALUES("Bob", "Orange"); INSERT INTO t VALUES("Bob", "Pear"); INSERT INTO t VALUES("Bob", "Lemon"); INSERT INTO t VALUES("Charlie", "Pear"); INSERT INTO t VALUES("Charlie", "Lemon"); 

The answer here will be Alice,Bob , because they took the same elements.

I want to do this with double negation (using NOT EXISTS / NOT IN), which I think is more suitable for this question, but I could not come up with anything that would be close to functionality.

This seems like this question , but I'm using SQLite, so I can't use GROUP_CONCAT () , but I was wondering how to do this using relational division, using NOT EXISTS / NOT IN.

Answer 1

With compound queries :

 SELECT t1.name, t2.name FROM t AS t1, t AS t2 GROUP BY t1.name, t2.name HAVING t1.name < t2.name AND NOT EXISTS (SELECT item FROM t WHERE name = t1.name EXCEPT SELECT item FROM t WHERE name = t2.name) AND NOT EXISTS (SELECT item FROM t WHERE name = t2.name EXCEPT SELECT item FROM t WHERE name = t1.name); 

Using NOT IN is possible, a bit expresses exactly the same mechanism with more complexity:

 SELECT t1.name, t2.name FROM t AS t1, t AS t2 GROUP BY t1.name, t2.name HAVING t1.name < t2.name AND NOT EXISTS (SELECT item FROM t WHERE name = t1.name AND item NOT IN (SELECT item FROM t WHERE name = t2.name)) AND NOT EXISTS (SELECT item FROM t WHERE name = t2.name AND item NOT IN (SELECT item FROM t WHERE name = t1.name)); 

Answer 2

To get the number of common elements between all pairs of names, you can use the following query:

 SELECT t1.name AS name1, t2.name AS name2, COUNT(*) AS cnt FROM t AS t1 INNER JOIN t AS t2 ON t1.item = t2.item AND t1.name < t2.name GROUP BY t1.name, t2.name 

Output:

 name1 name2 cnt ------------------------ Alice Bob 3 Alice Charlie 2 Bob Charlie 2 

Now you need to filter out the pairs (name1, name2) that have a counter that is not equal to the number of elements name1 and name2 . You can do this using the HAVING with correlated subqueries:

 SELECT t1.name AS name1, t2.name AS name2 FROM t AS t1 INNER JOIN t AS t2 ON t1.item = t2.item AND t1.name < t2.name GROUP BY t1.name, t2.name HAVING COUNT(*) = (SELECT COUNT(*) FROM t WHERE name = t1.name) AND COUNT(*) = (SELECT COUNT(*) FROM t WHERE name = t2.name) 

Demo here

Answer 3

Perhaps I found a solution to your problem. Mine was tested using MySQL, but it did not use GROUP_CONCAT (). It may work for your SQLite database. My query is used to search for people who bought the same items.

Try using this statement: SELECT DISTINCT e1.name, e2.name from t e1, t e2 WHERE e1.item=e2.item AND e1.name != e2.name GROUP BY e1.item HAVING count(*) >1;

https://gyazo.com/5e5e9d0ddfb33cb47439a674297108ed

Answer 4

This is similar to working with SQLLite

  select t1.name from t t1 join t t2 on t1.name <> t2.name and t1.item = t2.item join (select name, count(*) as cnt from t group by name) t3 on t3.name = t1.name join (select name, count(*) as cnt from t group by name) t4 on t4.name = t2.name group by t1.name, t3.cnt, t4.cnt having count(*) = max(t3.cnt, t4.cnt)