求一取数据交集的MYSQL语句

[jxyuhua]

大家好，
有3张表
dict 字典表
(
dict_id 自增长ID，
keyword 关键字
)

dict_to_product 对应关系表
(
dict_id,
product_id
)

product 产品表
(
product_id,
product_title
)

现在我想做一搜索，从dict中搜索关键字，查出符合条件的产品来。
keyword like '条件%'
分词的时候，如果有多个关键字，这时要取产品的交集，即符合所有条件的产品才取出来。
一条SQL能搞定吗？这个SQL怎么写？
我现在是分几次求出product_id, 然后用PHP数据求出它们的交集，觉得这样效率不高。
高手帮忙指点一下。

[WWWWA]

贴记录及正确结果贴出来看看

[ACMAIN_CHM]

select product_title
from (dict_to_product dp inner join product p on dp.product_id=p.product_id)
	inner join (
	select dict_id
	from dict 
	where keyword like '条件1%'
	or keyword like '条件2%'
	pr keyword like '条件3%') d on dp.dict_id=d.dict_id
group by product_title
having count(*)=3

[houweili]

select * from product p left join dict_to_product d on p.id=d.product_id left join dict c on d.dict_id=c.dict_id where c.keyword like '条件%'

[jxyuhua]

用几个数据来容易说得清楚些。
dict数据
dict_id keyword
32321 pcga
32362 bp71
32363 bp71a
32364 bp71auc
32365 bp71ce7

dict_to_product数据
product_id dict_id
214 32321
215 32321
216 32321
217 32321
216 32362
450 32362
216 32363
450 32363
product数据
product_id, product_title
214 产品1
215 产品2
216 产品3
217 产品4
450 产品5
当用户输入查询条件pcga bp71时，应该返回的正确结果是产品 216（产品3）
用一条SQL如何写？

[WWWWA]

select * from dict a inner join
dict_to_produc b on a.dict_id=b.dict_id
inner join product c on b.product_id=c.product_id
where a.keyword='pcga bp71'

[wwwwb]

有数据就好测试
select c.product_title from dict a inner join
dict_to_produc b on a.dict_id=b.dict_id
inner join product c on b.product_id=c.product_id
where a.keyword='pcga bp71'

[ACMAIN_CHM]

mysql> select * from dict;
+---------+---------+
| dict_id | keyword |
+---------+---------+
|   32321 | pcga    |
|   32362 | bp71    |
|   32363 | bp71a   |
|   32364 | bp71auc |
|   32365 | bp71ce7 |
+---------+---------+
5 rows in set (0.00 sec)

mysql> select * from dict_to_product;
+------------+---------+
| product_id | dict_id |
+------------+---------+
|        214 |   32321 |
|        215 |   32321 |
|        216 |   32321 |
|        217 |   32321 |
|        216 |   32362 |
|        450 |   32362 |
|        216 |   32363 |
|        450 |   32363 |
+------------+---------+
8 rows in set (0.00 sec)

mysql> select * from product;
+------------+---------------+
| product_id | product_title |
+------------+---------------+
|        214 | 产品1         |
|        215 | 产品2         |
|        216 | 产品3         |
|        217 | 产品4         |
|        450 | 产品5         |
+------------+---------------+
5 rows in set (0.00 sec)

mysql>
mysql> select product_title
    -> from (dict_to_product dp inner join product p on dp.product_id=p.product_id)
    ->     inner join (
    ->     select dict_id
    ->     from dict
    ->     where keyword = 'pcga'
    ->     or keyword = 'bp71') d on dp.dict_id=d.dict_id
    -> group by product_title
    -> having count(*)=2;
+---------------+
| product_title |
+---------------+
| 产品3         |
+---------------+
1 row in set (0.00 sec)

mysql>

[jxyuhua]

多谢ACMAIN_CHM，你使我明白了原来INNER JOIN还可以这样连接
inner join (
select dict_id
from dict
where keyword = 'pcga'
or keyword = 'bp71') d
on
感谢。

[jxyuhua]

还有其它帮忙解答的也十分感谢。