Mega Code Archive

 

Categories / MySQL / Data Type

 

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)