To identify SET values that share common elements : Set « Data Type « SQL / MySQL

1. SQL / MySQL
2. Data Type
3. Set

To identify SET values that share common elements

      
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  name    CHAR(20) NOT NULL,
    ->  birth   DATE,
    ->  color   ENUM('blue','red','green','brown','black','white'),
    ->  foods   SET('lutefisk','burrito','curry','eggroll','fadge','pizza'),
    ->  cats    INT,
    ->  PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mytable
    ->  VALUES
    ->          (NULL,'Jack','1970-04-13','black','eggroll,pizza,fadge',0),
    ->          (NULL,'Tom','1969-09-30','white','curry,eggroll,burrito',3),
    ->          (NULL,'Mary','1957-12-01','red','burrito,pizza,curry',1),
    ->          (NULL,'Jane','1973-11-02','red','pizza,eggroll',4),
    ->          (NULL,'Sean','1963-07-04','blue','burrito,curry',5),
    ->          (NULL,'Alan','1965-02-14','red',',curry,eggroll',1),
    ->          (NULL,'March','1968-09-17','green','fadge,lutefisk',1),
    ->          (NULL,'Shane','1975-09-02','black','pizza,curry',2),
    ->          (NULL,'Dan','1952-08-20','green','fadge,lutefisk',0),
    ->          (NULL,'Tony','1960-05-01','white','pizza,burrito',0);
Query OK, 10 rows affected, 1 warning (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 1

mysql>
mysql> SELECT t1.name, t2.name, t1.foods, t2.foods
    -> FROM mytable AS t1, mytable AS t2
    -> WHERE t1.id != t2.id AND (t1.foods & t2.foods) != 0
    -> ORDER BY t1.name, t2.name;
+-------+-------+-----------------------+-----------------------+
| name  | name  | foods                 | foods                 |
+-------+-------+-----------------------+-----------------------+
| Alan  | Jack  | curry,eggroll         | eggroll,fadge,pizza   |
| Alan  | Jane  | curry,eggroll         | eggroll,pizza         |
| Alan  | Mary  | curry,eggroll         | burrito,curry,pizza   |
| Alan  | Sean  | curry,eggroll         | burrito,curry         |
| Alan  | Shane | curry,eggroll         | curry,pizza           |
| Alan  | Tom   | curry,eggroll         | burrito,curry,eggroll |
| Dan   | Jack  | lutefisk,fadge        | eggroll,fadge,pizza   |
| Dan   | March | lutefisk,fadge        | lutefisk,fadge        |
| Jack  | Alan  | eggroll,fadge,pizza   | curry,eggroll         |
| Jack  | Dan   | eggroll,fadge,pizza   | lutefisk,fadge        |
| Jack  | Jane  | eggroll,fadge,pizza   | eggroll,pizza         |
| Jack  | March | eggroll,fadge,pizza   | lutefisk,fadge        |
| Jack  | Mary  | eggroll,fadge,pizza   | burrito,curry,pizza   |
| Jack  | Shane | eggroll,fadge,pizza   | curry,pizza           |
| Jack  | Tom   | eggroll,fadge,pizza   | burrito,curry,eggroll |
| Jack  | Tony  | eggroll,fadge,pizza   | burrito,pizza         |
| Jane  | Alan  | eggroll,pizza         | curry,eggroll         |
| Jane  | Jack  | eggroll,pizza         | eggroll,fadge,pizza   |
| Jane  | Mary  | eggroll,pizza         | burrito,curry,pizza   |
| Jane  | Shane | eggroll,pizza         | curry,pizza           |
| Jane  | Tom   | eggroll,pizza         | burrito,curry,eggroll |
| Jane  | Tony  | eggroll,pizza         | burrito,pizza         |
| March | Dan   | lutefisk,fadge        | lutefisk,fadge        |
| March | Jack  | lutefisk,fadge        | eggroll,fadge,pizza   |
| Mary  | Alan  | burrito,curry,pizza   | curry,eggroll         |
| Mary  | Jack  | burrito,curry,pizza   | eggroll,fadge,pizza   |
| Mary  | Jane  | burrito,curry,pizza   | eggroll,pizza         |
| Mary  | Sean  | burrito,curry,pizza   | burrito,curry         |
| Mary  | Shane | burrito,curry,pizza   | curry,pizza           |
| Mary  | Tom   | burrito,curry,pizza   | burrito,curry,eggroll |
| Mary  | Tony  | burrito,curry,pizza   | burrito,pizza         |
| Sean  | Alan  | burrito,curry         | curry,eggroll         |
| Sean  | Mary  | burrito,curry         | burrito,curry,pizza   |
| Sean  | Shane | burrito,curry         | curry,pizza           |
| Sean  | Tom   | burrito,curry         | burrito,curry,eggroll |
| Sean  | Tony  | burrito,curry         | burrito,pizza         |
| Shane | Alan  | curry,pizza           | curry,eggroll         |
| Shane | Jack  | curry,pizza           | eggroll,fadge,pizza   |
| Shane | Jane  | curry,pizza           | eggroll,pizza         |
| Shane | Mary  | curry,pizza           | burrito,curry,pizza   |
| Shane | Sean  | curry,pizza           | burrito,curry         |
| Shane | Tom   | curry,pizza           | burrito,curry,eggroll |
| Shane | Tony  | curry,pizza           | burrito,pizza         |
| Tom   | Alan  | burrito,curry,eggroll | curry,eggroll         |
| Tom   | Jack  | burrito,curry,eggroll | eggroll,fadge,pizza   |
| Tom   | Jane  | burrito,curry,eggroll | eggroll,pizza         |
| Tom   | Mary  | burrito,curry,eggroll | burrito,curry,pizza   |
| Tom   | Sean  | burrito,curry,eggroll | burrito,curry         |
| Tom   | Shane | burrito,curry,eggroll | curry,pizza           |
| Tom   | Tony  | burrito,curry,eggroll | burrito,pizza         |
| Tony  | Jack  | burrito,pizza         | eggroll,fadge,pizza   |
| Tony  | Jane  | burrito,pizza         | eggroll,pizza         |
| Tony  | Mary  | burrito,pizza         | burrito,curry,pizza   |
| Tony  | Sean  | burrito,pizza         | burrito,curry         |
| Tony  | Shane | burrito,pizza         | curry,pizza           |
| Tony  | Tom   | burrito,pizza         | burrito,curry,eggroll |
+-------+-------+-----------------------+-----------------------+
56 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
    
  

Related examples in the same category