CODEWITHSUNDEEP
sqlsqlitejoin
Nathan Arthur
Nathan Arthur

Reputation: 9096

SQL to find missing linking table rows

I'm new to SQL and having difficulty getting this query right. Simplified, my table structure is this:

Table A:
    aId primary key

Table B:
    bId primary key

Table A_B:
    aId foreign key A(aId)
    bId foreign key B(bId)

I'd like to create an SQL query that will tell me what combinations of A and B are not represented in linking table A_B. Additionally, each combination should only be shown once.

Below are the queries I've tried, none of which work. All of them begin repeating combinations after rows are added to the linking table.

SELECT A.aId, B.bId 
FROM A JOIN B JOIN A_B 
WHERE (A.aId!=A_B.aId OR B.bId!=A_B.bId)

SELECT A.aId, B.bId 
FROM A LEFT JOIN B LEFT JOIN A_B 
WHERE (A.aId!=A_B.aId OR B.bId!=A_B.bId)

SELECT A.aId, B.bId 
FROM A JOIN B JOIN A_B 
WHERE (A.aId!=A_B.aId AND B.bId!=A_B.bId)

How can I accomplish this?

Also, an explanation along with the solution would be much appreciated—as is probably evident, I have not yet completely wrapped my head around joins.

Upvotes: 0

Views: 85

Answers (1)

CL.
CL.

Reputation: 180121

With these WHEREs, you are checking whether any combination of rows has these values.

However, "not represented in" is something different; you actually want to check whether all rows do not have these values.

This requires an operator like EXISTS:

SELECT A.aId, B.bId
FROM A CROSS JOIN B
WHERE NOT EXISTS (SELECT 1
                  FROM A_B
                  WHERE aId = A.aID
                    AND bId = B.bID)

Upvotes: 2

Related Questions