Many-to-Many Relation - Select all A except for those linked to B

Question

I have a many-to-many relationship between items A and B in my database

     A                B               A_B_Join
============     ============     ===============
 Id  |  ...       Id  |  ...       A_Id  |  B_Id
------------     ------------     ---------------
  0  |  ...        5  |  ...          0  |  5
  1  |  ...        6  |  ...          0  |  7
  2  |  ...        7  |  ...          1  |  5
  3  |  ...        8  |  ...          1  |  6
                                      2  |  6
     :                :               2  |  7
     :                :               3  |  7
                                      3  |  8
                                      3  |  5

I am trying to get for example, all As that don't have a corresponding value of B from an arbitrary list, say, [7, 8]

What I have so far that is incorrect is:

SELECT A.* FROM A INNER JOIN A_B_Join ON A.id=A_B_Join.A_id WHERE A_B_Join.B_Id NOT IN (:BIds)

where BIds is my list [7, 8]. However this isn't exactly correct since it will still select those specific A rows which don't a corresponding B_id value specified in BIds

I have tried searching for examples and solutions for problems similar to mine but I'm not sure what this type of Query is called. I would appreciate any help with this or pointers to any duplicate questions

Answer 1

You can use NOT EXISTS. The inner query will get all As that has 7 or 8 but since NOT EXISTS was use, it will display all rows from A that is not present in the inner query.

SELECT  a.*
FROM    a
WHERE   NOT EXISTS (SELECT 1
                    FROM A_B_Join b
                    WHERE a.ID = b.A_ID
                            AND b.B_ID IN (7, 8))