How select rows with same values from table for some rows?

Question

Table structure and value are given below:

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(11) NOT NULL,
  `id_sub` int(11) NOT NULL,
  `id_obj` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `id_sub`, `id_obj`) VALUES
  ('1','1', '1'),
  ('2','1', '2'),
  ('3','1', '3'),
  ('4','1', '10'),
  ('5','1', '1'),
  ('6','1', '10'),
  ('7','1', '3'),
  ('8','1', '10'),
  ('9','1', '1'),
  ('10','2', '30'),
  ('11','2', '10'),
  ('12','2', '1'),
  ('13','2', '10'),
  ('14','4', '1'),
  ('15','5', '1'),
  ('16','6', '1'),
  ('17','7', '1'),
  ('18','7', '10'),
  ('19','7', '11');

Next query select rows where I know id_sub and id_obj have same values:

SELECT 
    * 
FROM docs
WHERE `id_sub` IN (1,2,7)
AND `id_obj` IN (SELECT `id_obj` FROM `docs` GROUP BY 1 HAVING count(*)>1)

results:

   id  |  id_sub  |  id_obj
   1   |    1     |  1
   3   |    1     |  3
   4   |    1     |  10
   5   |    1     |  1
   6   |    1     |  10
   7   |    1     |  3
   8   |    1     |  10
   9   |    1     |  1
   11  |    2     |  10
   12  |    2     |  1
   13  |    2     |  10
   17  |    7     |  1
   18  |    7     |  10

But I can not select rows where I know id_sub where all id_obj have same values, ie I can not get next result:

   id  |  id_sub  |  id_obj
   1   |    1     |  1
   4   |    1     |  10
   11  |    2     |  10
   12  |    2     |  1
   17  |    7     |  1
   18  |    7     |  10

Table have ~ 20k rows, count values for IN() can be different.

Any ideas?

---

UPD - description conditions

i know values for condition id_sub - 1,2 or 7 (example)

i do not know values id_obj

conditions for expected result:

1. values id_sub 1, 2 or 7
2. rows do not have dublicated values id_sub&id_sub, ie rows with id = 1 and id = 5 is dublicate(see values rows) and result can be has only one row with id = 1 (if rows duplicated need return first duplicate row)

3. id_obj values should be have in each row result, ie: now in result i have:

   id | id_sub | id_obj

   1 | 1 | 1

   2 | 1 | 2

   3 | 1 | 3

as we can see in this rows only row with id= 1 has id_obj value which have in other rows:

id  |  id_sub  |  id_obj
1   |    1     |  1
12  |    2     |  1
17  |    7     |  1

And for all table i would like get next result:

Sql fiddle table with rows

Answer 1

I think you’re simply after this... but I may have misunderstood ...

select a.*
  from docs a
  join (
       SELECT id_obj
         FROM docs
        WHERE `id_sub` IN (1,2,7)
        group 
           by id_obj
       having count (distinct id_sub) = 3
       ) b
 on b.id_obj = a.id _obj;

Answer 2

To get the result I think you want your source data would have to be

drop table if exists docs;

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(11) NOT NULL,
  `id_sub` int(11) NOT NULL,
  `id_obj` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `id_sub`, `id_obj`) VALUES
  ('1','1', '1'),
  ('2','1', '2'),
  ('3','1', '3'),
  ('4','1', '10'),
  ('5','1', '1'),
  ('6','1', '10'),
  ('7','1', '3'),
  ('8','1', '10'),
  ('9','1', '1'),
  ('10','2', '30'),
  ('11','2', '10'),
  ('12','2', '1'),
  ('13','2', '10'), (20,2,'1'),
  ('14','4', '1'),
  ('15','5', '1'),
  ('16','6', '1'),
  ('17','7', '1'), (21,7,1),
  ('18','7', '10'), (22,7,10),
  ('19','7', '11');

And the code to produce the expected result

select  min(id) id ,id_sub,id_obj
from docs
#where id_sub = 1
group by id_sub,id_obj
having count(*)  > 1
;

+------+--------+--------+
| id   | id_sub | id_obj |
+------+--------+--------+
|    1 |      1 |      1 |
|    3 |      1 |      3 |
|    4 |      1 |     10 |
|   12 |      2 |      1 |
|   11 |      2 |     10 |
|   17 |      7 |      1 |
|   18 |      7 |     10 |
+------+--------+--------+
7 rows in set (0.00 sec)

Answer 3

I found a running query here.

SELECT MIN(a.id) AS id, a.id_sub, a.id_obj
FROM docs a
     JOIN (
        SELECT id_obj
        FROM docs
        WHERE id_sub IN (1,2,7)
        GROUP BY id_obj
        HAVING COUNT(DISTINCT id_sub) = 3
      ) b
      ON a.id_obj = b.id_obj
WHERE id_sub IN (1,2,7)
GROUP BY a.id_sub, a.id_obj

But I am not sure is this the best solution.
Too many subquery I guest. But better than nothing.
Hope this help.