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)
|