CODEWITHSUNDEEP
mysqlsqlselect
Somnium
Somnium

Reputation: 1145

SQL select row in one table, for whose key row in another table has given values

It is difficult to explain what I want in the title, some I'll try to do it with an example here. I have 2 tables:

[Table1]
set_id | data
-------+-----
   1   | 123
   2   | 456
   3   | 789
   4   | 987

[Table2]
set_id | single_id
-------+----------
   1   |    10
   2   |    10
   2   |    13
   3   |    10
   3   |    13
   3   |    14
   4   |    10
   4   |    15

I need to select row in Table1 with such set_id that in Table2 rows with same set_id have single_ids only those given in query. For example:

For query (10, 13) resulting row should be 2 | 456.

For query (10) resulting row should be 1 | 123.

For query (10, 13, 14) resulting row should be 3 | 789.

How this can be done?

Upvotes: 0

Views: 284

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is an example of a set-within-sets subquery. I think the most general approach is to use aggregation with a having clause:

select t1.set_id, t1.data
from table1 t1 join
     table2 t2
     on t1.set_id = t2.set_id
group by t1.set_id
having sum(t2.single_id = 10) > 0 and
       sum(t2.single_id = 13) > 0 and
       sum(t2.single_id not in (10, 13)) = 0;

Each condition in the having clause tests one condition. The first is that a row with 10 is present; the second that a row with 13 is present. And the last that no other values are present.

EDIT:

In MySQL, there is actually another approach which might seem more intuitive:

select t1.set_id, t1.data
from table1 t1 join
     table2 t2
     on t1.set_id = t2.set_id
group by t1.set_id
having group_concat(distinct t2.single_id order by t2.single_id) = '10,13';

That is, concatenate the distinct values together, in order, and compare them to a constant string.

Upvotes: 2

Related Questions