Different mysql column in multiple row conditions

Question

I have a mysql database (item_preset) with the following table:

+-----+-----------+---------+-------+ | id | preset_id | item_id | value | +-----+-----------+---------+-------+ | 1 | 1 | 1 | 2 | | 2 | 1 | 2 | 1 | | 3 | 1 | 4 | 60 | | 4 | 1 | 3 | 16 | | 5 | 1 | 3 | 17 | | 6 | 1 | 3 | 18 | | 7 | 1 | 3 | 25 | | 8 | 1 | 3 | 26 | | 9 | 1 | 3 | 27 | | 10 | 2 | 1 | 3 | | 11 | 2 | 2 | 0 | | 12 | 2 | 4 | 0 | | 13 | 2 | 3 | 16 | | 14 | 2 | 3 | 17 | | 15 | 2 | 3 | 19 | | 16 | 2 | 3 | 20 | | 17 | 2 | 3 | 21 | | 18 | 3 | 1 | 2 | | 19 | 3 | 2 | 0 | | 20 | 3 | 4 | 0 | | 21 | 3 | 3 | 25 | | 22 | 3 | 3 | 28 | | 23 | 4 | 1 | 1 | | 24 | 4 | 2 | 1 | | 25 | 4 | 4 | 120 | | 26 | 4 | 3 | 16 | | 27 | 4 | 3 | 17 | | 28 | 4 | 3 | 18 | | 29 | 4 | 3 | 22 | | 30 | 4 | 3 | 23 | | 31 | 4 | 3 | 24 | | 32 | 6 | 1 | 3 | | 33 | 6 | 2 | 1 | | 34 | 6 | 4 | 90 | | 35 | 6 | 3 | 18 | | 36 | 6 | 3 | 22 | | 37 | 6 | 3 | 23 | | 38 | 6 | 3 | 24 | | 39 | 6 | 3 | 29 | | 40 | 6 | 3 | 30 | +-----+-----------+---------+-------+ 

What I would like to do is get a separate preset_id based on conditions from several lines. for example, to get preset_id 1, I need all the conditions to be true (item_id = 1 and value_id = 2), (item_id = 2 and value = 1), etc.

I tried using the following: select a separate preset_id from item_preset, where (item_id = 1 and value = 2) and (item_id = 2 and value = 1) and (item_id = 4 and value = 60);

but get an empty set. If I try to use Or instead, and I get all preset_ids that match any of the conditions.

Any ideas?

thanks

Answer 1

You can try something like this:

  select distinct preset_id from item_preset where preset_id in (select preset_id from item_preset where item_id = 1 and value = 2) and preset_id in (select preset_id from item_preset where item_id = 2 and value = 1) and preset_id in (select preset_id from item_preset where item_id = 4 and value = 60); 

Answer 2

You might want to try this thing,

 SELECT preset_id FROM tableName WHERE (item_id = 1 and value = 2) OR (item_id = 2 and value = 1) OR (item_id = 4 and value = 60) GROUP BY preset_id HAVING COUNT(*) = 3 

SQLFiddle Demo

Answer 3

More brackets

 select distinct(preset_id) from item_preset where ((item_id = 1 and value = 2) or (item_id = 2 and value = 1) or (item_id = 4 and value = 60)); 

and keyword value - do not use value as column name