There are two tables: items
item_id | item_name | ...item_flags
item_id | flag_name | flag_value
Need to get all items, which included some flags (just two).
Now it is done like this:
SELECT * FROM items
JOIN item_flags as f1 ON (items.item_id = f1.item_id)
JOIN item_flags as f2 ON (items.item_id = f2.item_id)
( f1.flag_name = '...' AND f1.flag_value = '...' ) AND
( f2.flag_name = '...' AND f2.flag_value = '...' )
GROUP BY items.item_id
That is, for each of the next flag to make a new JOIN, and then it is grouped by the item.
But I think that this is some very stupid and wrong and you can make it easier.