I have a structure setup where conditions can have multiple values (OR claused) and all conditions must be met (AND clause), the amount of conditions is dynamic and can increase or decrease with each query.
I want to perform a lookup on the join table of Items and Condition_Values (items_values) where an Item meets all the conditions. The link itself is from Items to Condition_Value and I am preformatting the SQL to group condition_value_ids into proper claused groups like so:
(1 OR 5 OR 6 OR 9), (2 OR 10 OR 15), (9 OR 22 OR 33)
to be ANDed together at some point.
I am looking for a way to make SQL query that selects a distinct Item_Id where all conditions are met on it being linked to at least one of the condition_value_ids in each group.
So a table like so:
ITEM_ID CONDITION_VALUE_ID 1 1 1 5 1 10 1 22 1 33 2 5 2 9 2 55 3 9 3 15 3 22
Would return me ItemS with IDs 1 and 3 (since items 1/3 matched at least 1 condition_value_id link in each condition group, 2 does not have at least 1 match in every group). My best guess would be something of a gigantic mess of inner statements doing a check like
SELECT * FROM items_values WHERE EXISTS( SELECT TOP 1 1 FROM items_values WHERE condition_value_id IN (1, 5, 6, 9)) ) AND .....
Alternatively, I could take a flat list of condition_value_ids GROUP BY with item_id and do the search manually in PHP, but that seems excessive to collect every item, every search.
Any suggestions in the right direction would be greatly appreciated.
Something like this (Modified version of what they have below):
SELECT ITEM_ID,GROUP_CONCAT(CONDITION_VALUE_ID) AS VALUE_IDS, COUNT(CONDITION_VALUE_ID) FROM TEST GROUP BY ITEM_ID HAVING COUNT(CONDITION_VALUE_ID) > 1;
And then doing a LIKE on each VALUE_IDS would be possible, or just doing in_array checks to match each condition in PHP, but I would prefer if it could be done inside the query.