There is a table

item_id, division_id 1 1 1 2 2 1 3 1 4 1

How to choose item_id, which go to division_id = 1 and division_id = 2? THOSE. Should there be a unique item_id that only applies to 2 or more sections?

  • Give an example of the resulting sample - vp_arth
  • Select * from table where division_id = 1 Get 1,2,3,4 Select * from table where division_id = 2 Get 1 The final answer should be 1 - there are both there and there - Gleb
  • There are hundreds of such questions. just a little bit different. I just won’t decide which way to double ... ru.stackoverflow.com/… - Mike
  • Close the question, I will rewrite it otherwise! - Gleb
  • Rewrite this, why produce questions? - vp_arth

2 answers 2

Using DISTINCT, you can select unique values:

 SELECT DISTINCT(`item_id`) FROM `table` WHERE `division_id` IN(1,2)
  • It will give out both the goods of group 1 and the goods of group 2, but you need goods belonging to both group 1 and group 2 at the same time! - Gleb

item_id by item_id , count in how many division_id each enters, take only those that are 2 or more.

 SELECT item_id FROM `table` GROUP BY item_id HAVING COUNT(division_id) > 1

For a sample by specific division_id you can add

 WHERE division_id IN (1, 2)

Brute force method:

 SELECT item_id FROM `table` t WHERE EXISTS(SELECT * FROM `table` a where a.item_id=t.item_id AND a.division_id IN (1,2,3,4,5)) AND EXISTS(SELECT * FROM `table` a where a.item_id=t.item_id AND a.division_id = 6)
  • Gives out correctly. And if division_id is not 2, but more? Ideally, it should be division_id IN (1, 2, 3, 4, 5) AND division_id = 6, i.e. IN (...) is standard, and then, sometimes you need to add a second section (only one) - Gleb
  • Why was this not in question? - vp_arth
  • @Gleb, The last edited answer is definitely not suitable? =) - vp_arth
  • This is roughly what is needed, but for some reason it gives all-all table entries - Gleb
  • It’s like this: SELECT item_id FROM table WHERE division_id IN (1) AND item_id IN (SELECT item_id FROM table WHERE division_id = 2) - Gleb