Mysql select row based on multiple rows in same table

Question

I have the following table structure:

item_id  | value |  
==================  
1        |   1   |  
1        |   3   |  
1        |   4   |  
2        |   2   |  
2        |   3   |  
2        |   4   |  
2        |   5   |  
3        |   1   |  
3        |   5   |  
3        |   6   |  
4        |   1   |  
4        |   3   |  
4        |   4   |  
4        |   5   |

I have a query that returns those item_id whose value matches with 1, 3 and 4. So here, the item_ids that should be returned are 1 and 4.

My query:

select item_id from table t
where exists (select item_id from table t1 where value = 1 and t1.item_id = t.item_id)
and exists (select item_id from table t1 where value = 2 and t1.item_id = t.item_id) group by item_id

This query is working fine. Here i am matching only 3 values. What if i want to match 50 such values from the table? (all the 50 values are stored in a php array) The query will be huge and also i want to do the same thing from two different tables in the same query. So, this will double the size of an already huge query. Please suggest me some other way around.

Edited::

table 2
--------

item_id  | user_id |  
==================  
1        |   1   |  
1        |   5   |  
1        |   7   |  
2        |   2   |  
2        |   3   |  
2        |   4   |  
2        |   5   |  
3        |   1   |  
3        |   5   |  
3        |   6   |  
4        |   1   |  
4        |   3   |  
4        |   4   |  
4        |   5   |

Now, i want item_id where values from table1 are 1,3,4 and user_id from table2 are 1,5,7

John Woo · Accepted Answer · 2013-05-14 06:24:45Z

7

This problem is called Relational Division.

SELECT  item_ID
FROM    tableName
WHERE   value IN (1,3,4)
GROUP   BY item_ID
HAVING  COUNT(*) = 3

if uniqueness was not enforce on column value for every item_id, DISTINCT is required to count only unique values,

SELECT  item_ID
FROM    tableName
WHERE   value IN (1,3,4)
GROUP   BY item_ID
HAVING  COUNT(DISTINCT value) = 3

answered May 14, 2013 at 6:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kshitij Over a year ago

+1.. i ended up with the same having query as urs ... so deleted :)

kunal khemka Over a year ago

I mentioned earlier that i want to do the same thing from two different tables in the same query. I am stuck in that. I have another table (table2) with same structure (item_id and value). Now i want those item_ids that have value from table1 as 1,2,3 and value from table2 as 3,4,5,7

John Woo Over a year ago

can you give example on that just like what you posted on your question? So in short you want to get the item_id which contains 1,2,3,4,5,7? or what?

John Woo Over a year ago

can you update your question with your sample records? please :)

kunal khemka Over a year ago

no. Both the tables are separate. i'll change the column names so that there will be no confusion. in table 2, i have columns item_id and user_id Now i want those item_ids that have value from table1 as 1,2,3 and user_id from table2 as 3,4,5,7

|

Collectives™ on Stack Overflow

Mysql select row based on multiple rows in same table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related