Three solutions, the first one is not recommended I just showed it for
fun -- I think the last one is the most efficient:
mysql> show create table job \G
*************************** 1. row ***************************
Table: job
Create Table: CREATE TABLE `job` (
`job_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(12) default NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table color \G
*************************** 1. row ***************************
Table: color
Create Table: CREATE TABLE `color` (
`color_id` int(10) unsigned NOT NULL auto_increment,
`color` varchar(32) default NULL,
`job_id` int(10) unsigned default NULL,
PRIMARY KEY (`color_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
mysql> select * from job;
+--------+-------+
| job_id | name |
+--------+-------+
| 1 | job 1 |
| 2 | job 2 |
| 3 | job3 |
+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from color;
+----------+---------+--------+
| color_id | color | job_id |
+----------+---------+--------+
| 1 | yellow | 1 |
| 2 | cyan | 1 |
| 3 | black | 1 |
| 4 | cyan | 2 |
| 5 | magenta | 2 |
| 6 | black | 2 |
| 7 | yellow | 2 |
| 8 | cyan | 3 |
+----------+---------+--------+
8 rows in set (0.00 sec)
Method #1
mysql> select job_id, GROUP_CONCAT(color) as colors FROM job j JOIN color c
USING (job_id) GROUP BY job_id HAVING colors LIKE '%cyan%magenta';
+--------+---------------------------+
| job_id | colors |
+--------+---------------------------+
| 2 | black,yellow,cyan,magenta |
+--------+---------------------------+
1 row in set (0.01 sec)
Method #2
SELECT j.job_id, c1.color, c2.color FROM job j JOIN color c1 ON
j.job_id=c1.job_id AND c1.color='cyan' JOIN color c2 ON j.job_id=c2.job_id AND
c2.color='magenta';
+--------+-------+---------+
| job_id | color | color |
+--------+-------+---------+
| 2 | cyan | magenta |
+--------+-------+---------+
1 row in set (0.00 sec)
Method #3
mysql> SELECT job_id, BIT_OR(CASE WHEN c.color='cyan' THEN 1 WHEN
c.color='magenta' THEN 2 END) as colors FROM job j JOIN color c USING (job_id)
GROUP BY job_id HAVING colors=3;
+--------+--------+
| job_id | colors |
+--------+--------+
| 2 | 3 |
+--------+--------+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
