CODEWITHSUNDEEP
mysqlsql
devnull Ψ
devnull Ψ

Reputation: 4029

select record only if specific related values are provided in mysql

I 3 have tables, 2(A and B) of them have many to many relationship, they connected through pivot C table. tables desc:

A(id, name)
B(id, is_required)
C(a_id, b_id)

I want to select records from A table, which related record ids from B table are in provided input and fit some condition. for example: lest say I have list of integers(ids) [1,2,3,4,8,12] and also one record from A has 5 related records from B, example:

A
id    name
1     test
-------------
B
id   is_required
1    true
2    true
3    false
10   false
16   false

I need to select records from table A join related records from table B, and check - if all required(is_required = true) record ids from B exists in my list ([1,2,3,4,8,12]) then we select this record, otherwise not. so the first example should be selected, because all required records from B (1 and 2) exists in list. for example this:

A
id    name
2     test2
-------------
B
id   is_required
1    true
2    true
5    true
6    false

should not be selected, because required record with id 5 not provided in list. how can I implement this in mysql? query example:

SELECT A.id, A.name FROM A, B, C
WHERE A.id = C.a_id
AND  C.b_id = B.id

as you see, for now its only joins related data, I really don't know how should I implement this. can you please help me?

Upvotes: 0

Views: 321

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use group by and having:

select c.a_id
from c join
     b
     on c.b_id = b.id and b.is_required = 'true'
group by c.a_id
having count(*) = (select count(*) from b where b.is_required = 'true');

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I believe that you need NOT EXISTS

select A.*
from A
where not exists(
  select 1
  from C
  join B on C.b_id = B.id and
            A.id = C.a_id and
            is_required = 'false'
)

Upvotes: 1

Related Questions