MySQL strict select of rows involving many to many tables

Question

I have three tables in the many-to-many format. I.e, table A, B, and AB set up as you'd expect.

Given some set of A ids, I need to select only the rows in AB that match all of the ids.

Something like the following won't work:

"SELECT * FROM AB WHERE A_id = 1 AND A_id = 2 AND A_id = 3 AND ... " As no single row will have more than one A_id

Using, an OR in the sql statment is no better as it yields results all results that have at least one of the A ids (whereas I only want those rows that have all of the ids).

Edit:

Sorry, I should explain. I don't know if the actual many-to-many relationship is relevant to the actual problem. The tables are outlined as follows:

Table People int id char name

Table Options int id char option

Table peoples_options int id int people_id int option_id

And so I have a list of people, and a list of options, and a table of options and people.

So, given a list of option ids such as (1, 34, 44, ...), I need to select only those people that have all the options.

Answer 1

Your database doesn't appear to be normalized correctly. Your AB table should have a single A_id and a single B_id in each of its rows. If that were the case, your OR-version should work (although I would use IN myself).

Ignore the preceding paragraph. From your edit, you really wanted to know all the B's that have all of a subset of A's in the many-to-many table - see below for the query.

Please tell us the actual schema details, it's a little hard to figure out what you want without that.

I'd expect to see something like:

table a:
    a_id integer
    a_payload varchar(20)
table b:
    b_id integer
    b_payload varchar(20)
table ab:
    a_id integer
    b_id integer

Based on your description, the only thing I can think of is that you want a list of all the B's that have all of a set of A's in the AB table. In which case, you're looking at something like (to get the list of B's that have A's of 1, 3 and 4):

select distinct b_id from ab n1             
    where exists (select b_id from ab where a_id = 1 and b_id = n1.b_id)     
    and   exists (select b_id from ab where a_id = 3 and b_id = n1.b_id)     
    and   exists (select b_id from ab where a_id = 4 and b_id = n1.b_id);

This works in DB2 but I'm not sure how much of SQL your chosen server implements.

Answer 2

A bit of a hacky solution is to use IN with a group by and having filter. Like so:

SELECT B_id FROM AB
WHERE A_id IN (1,2,3)
GROUP BY B_id
HAVING COUNT(DISTINCT A_id) = 3;

That way, you only get the B_id values that have exactly 3 A_id values, and they have to be from your list. I used DISTINCT in the COUNT just in case (A_id, B_id) isn't unique. If you need other columns, you could then join to this query as a sub-select in the FROM clause of another select statement.