CODEWITHSUNDEEP
mysqlsql
cjds
cjds

Reputation: 8426

Query to find if the rows in which a subset of larger set exists

There must be a simple solution to this that I can't find or figure out but its driving me insane

There is a table A

id 
1
2
3

There is a table B

id 
a
b
c

There is many to many relation between them table A_B

a_id    b_id
 1       a
 1       c
 1       b
 2       a
 2       c
 3       b

I want a query that has an input of a set of B's tuples and figures out which of A's tuples have that as a subset.

For example

Input c
Output {1,2}

Input {a,c}
Output {1,2}

Input {a,b}
Output {1}

Input {b}
Output {1,3}

The only way I've come up with for doing this is selecting all the rows for each individual tag and performing an intersection on these rows. That seems inefficient.

Upvotes: 0

Views: 80

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

You can inner join your A_B table to itself on the a_id, which will provide an intersection of that value between the provided b_id values:

select c.a_id
from A_B as c
where c.b_id = 'c';

select a.a_id
from A_B as a
  inner join A_B as c
    on c.a_id = a.a_id
where a.b_id = 'a'
  and c.b_id = 'c';

select a.a_id
from A_B as a
  inner join A_B as b
    on b.a_id = a.a_id
where a.b_id = 'a'
  and b.b_id = 'b';

select b.a_id
from A_B as b
where b.b_id = 'b';

SQL FIDDLE

Upvotes: 0

jpw
jpw

Reputation: 44891

This query should do what you want; the number of items in the in list must match the argument to having:

select a_id from table_A_B
where b_id in ('a','c')
group by a_id having count(distinct b_id) = 2

The distinct is only needed if you allow duplicate rows.

See this sample SQL Fiddle with your test cases.

Upvotes: 4

Related Questions