CODEWITHSUNDEEP
mysql
BernardA
BernardA

Reputation: 1523

mysql how to select items from Table A where all corresponding items in Table B satisfy condition

I need to obtain all elements m_id of Table A where m_active is = N and that the corresponding elements in Table B have ALL v_active = N.

m_id is foreign key in Table B.

In the example below, what I am looking for is m_id=2 and and m_id=4 as both satisfy the condition of being m_active=N and have ALL v_active = N.

How do I go about that?

Thanks

Table A example:

m_id     m_active   
1           Y
2           N
3           Y
4           N

Table B example:

v_id    m_id    v_active
 1       1         N
 2       1         Y
 3       1         N
 4       2         N
 5       2         N
 6       2         N
 7       3         N
 8       3         Y
 9       3         Y
10       4         N

Upvotes: 1

Views: 90

Answers (2)

ahoo
ahoo

Reputation: 1361

Try this:

SELECT * FROM A 
WHERE m_active='N'
AND NOT EXISTS (
    SELECT * FROM B 
    WHERE B.m_id=A.m_id
    AND B.v_active<>'N'
);

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425371

SELECT  *
FROM    a
WHERE   m_active = 'N'
        AND m_id NOT IN
        (
        SELECT  m_id
        FROM    b
        WHERE   v_active <> 'N'
        )

This will also select all entries from a which have no corresponding entries in b (and hence all 0 of 0 entries are inactive). This may or may not be what you want.

Upvotes: 2

Related Questions