attadyScafeft
New Member
I'm trying to do something like the PHP \[code\]array_intersect\[/code\]. I have the following table\[code\]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=MyISAMAVG_ROW_LENGTH=47 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; \[/code\]I need a query that will get me all the results that contain say id 2,72,64 as a distinct itemid. The thing is that an id may exist more than once in an itemid, i.e itemid 600 may have 3 rows with ids 2,100,2 asociated with it.
My idea was to have a query with say x number o subqueries returning results and doing something like the PHP \[code\]array_intersect\[/code\], thus returning the itemids that have all these ids. \[code\]SELECT DISTINCT itemid FROM recipes_ingredients_itemsWHERE 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)\[/code\]This is what I got and it is 100% wrong.
My idea was to have a query with say x number o subqueries returning results and doing something like the PHP \[code\]array_intersect\[/code\], thus returning the itemids that have all these ids. \[code\]SELECT DISTINCT itemid FROM recipes_ingredients_itemsWHERE 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)\[/code\]This is what I got and it is 100% wrong.