mysql相交_PHP-Mysql相交结果

我正在尝试做类似PHP array_intersect的操作.我有下表

CREATE TABLE `recipes_ingredients_items` (

`id` INTEGER(11) DEFAULT NULL,

`itemid` INTEGER(11) DEFAULT NULL,

`orderby` TINYINT(4) NOT NULL,

KEY `id` (`id`),

KEY `itemid` (`itemid`)

)ENGINE=MyISAM

AVG_ROW_LENGTH=47 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

我需要一个查询,该查询将使我得到所有包含说ID 2,72,64作为不同itemid的结果.问题是,一个id在itemid中可能不止一次存在,即itemid 600可能有3行与ID相关的ID为2、100、2.

我的想法是用x数o子查询进行查询,以返回结果并执行类似PHP array_intersect的操作,从而返回具有所有这些ID的itemid.

SELECT DISTINCT itemid FROM recipes_ingredients_items

WHERE

id IN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 71)

AND

id IN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 2)

AND

id IN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 22)

这就是我得到的,这是100％错误的.