CODEWITHSUNDEEP
mysql
John Carroll
John Carroll

Reputation: 156

MYSQL Query for finding rows without certain associated rows in another table.

I am sure the answer is out there somewhere, but I am having a hard time articulating what I need, so I figured it best to give an example. Take these two tables for instance:

     PLAN TABLE          OTHER TABLE ASSOCIATED TO PLAN
___________________      ____________________________
|  PK  |  planID  |      |  PK  |  planID  |  flag  |
-------------------      ----------------------------
|  1   |    51    |      |  1   |    51    |    0   |
|  2   |    62    |      |  2   |    51    |    1   |
|  3   |    73    |      |  3   |    51    |    1   |
-------------------      |  4   |    62    |    0   |
                         |  5   |    62    |    0   |
                         |  6   |    62    |    1   |
                         |  7   |    73    |    0   |
                         |  8   |    73    |    0   |
                         |  9   |    73    |    0   |
                         ----------------------------

What I am struggling with is writing a query that will return to me all of the planIDs from the plan table that do not have an entry in the associated table with flag = 1. So in this case the result of the query would be:

____________
|  planID  |
------------
|    73    |
============

Upvotes: 0

Views: 28

Answers (1)

A.D.
A.D.

Reputation: 1180

I think you need something like that (not tested) :

select planId
from plan
where not exist (
    select 1
    from other_table
    where plan.planId = other_table.planId
    and flag = 1
)

Upvotes: 1

Related Questions