我有一个类似的mysql表
PRODUCT
-------
product_id: 1 | name: product .... product_id: 2 | name: product2 .. etc etc
然后我有另一张桌子
PROPERTY
---
id: 1 | name: red | product_id: 1 .... id: 2 | name: large | product_id: 1 ..... id: 3 | name : brown | product_id: 2 .... and so on ...
如何选择具有2个属性的产品?
例如,我想拿一个具有RED和LARGE属性的产品
我试过
SELECT * FROM product
JOIN property USING (product_id)
WHERE property.name = "red" AND property.name = "large" ....
但显然它不起作用:)
谢谢你的帮助,对不起我的英语!
您需要计算其记录的实例
SELECT a.productname
FROM product a
INNER JOIN property b
on a.product_ID = b.product_ID
WHERE b.name IN ('red', 'large')
GROUP BY a.productname
HAVING COUNT(a.productname) = 2
如果单击演示,您可以看到对结果进行分组和使用IN
进行常规归档之间的差异
SQLFiddle演示
您应该使用OR
:
SELECT * FROM product
JOIN property USING (product_id)
WHERE property.name = "red" OR property.name = "large" ....
此外,您还可以使用IN
:
SELECT * FROM product
JOIN property USING (product_id)
WHERE property.name IN ('red', 'large') ....
也许类似
SELECT p.*
FROM product p
WHERE EXISTS( SELECT *
FROM property a
WHERE a.name = 'red'
AND a.product_id = p.product_id)
AND EXISTS( SELECT *
FROM property a
WHERE a.name ='large'
AND a.product_id = p.product_id)
可以工作吗?