菜鸟求助,根据关系表的多重搜索~!

[lookearth]

table A(分类表)
id category_name
1 分类1
2 分类2
3 分类3
4 分类4
5 分类5
6 分类6

table B(关系表)
id category_id product_id
1 2 1
2 3 1
3 4 1

4 2 2
5 4 2
6 5 2
6 6 2

7 1 3
8 3 3
9 4 3
10 6 3

table C (产品表)
id product_name
1 产品1
2 产品2
3 产品3


如何根据关系搜索有分类2,4的产品,只用一条sql语句可以吗?
本人是初学者,谢谢大家帮助;

[shoppo0505]

select A.*,C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id in (2,4)

[lookearth]

引用 1 楼 shoppo0505 的回复:

select A.*,C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id in (2,4)

你好,你可能误解了我的意思; 我想找出的是有 分类2 并且有 分类4 的产品;如 产品1 和 产品3 而并不是有 分类2 或者 分类4 的产品

[shoppo0505]

select C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id = 2 intersect select C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id = 4

[lookearth]

引用 3 楼 shoppo0505 的回复:

select C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id = 2 intersect select C.* from A inner join B on B.category_id = A.id inner join C on B.product_id = C.id where A.id = 4

不好意思..发错地方了,以为这是mysql的版块

[Ginnnnnnnn]

用3楼的方法或者

SELECT *
	FROM C
		WHERE EXISTS(SELECT * 
							FROM B b1
								INNER JOIN B b2 ON b1.product_id = b2.product_id 
						WHERE b1.category_id = 2
						  AND b2.category_id = 4
						  AND b1.product_id = C.id)