Reputation: 126
OK. So as I'm writing this query, I realize it's unlike any other I've written because of the table structure. Two of three tables I'm trying to bring together have references to both of the others, and one of said references is nullable.
Table A: aID (PK), etc
Table B: bID (PK), aID (FK), etc
Table C: cID, aID (FK), bID (nullable FK), etc
In my query, I'm trying to locate all A
and B
rows that are not referenced in a C
row.
I've tried all sorts of stuff, the least of which was right joins, which I don't particularly enjoy. I also contemplated some sort of JOIN ON x AND y
- but I'm not clear on what that does, and documentation is scarce.
E.G.
Table A
aID
1
2
3
Table B
bID aID
1 1
2 1
3 2
Table C
cID aID bID
1 1 NULL
2 1 1
3 2 NULL
Query should return
aID bID
1 2
2 3
3 NULL
Upvotes: 0
Views: 99
Reputation: 460380
I'm trying to locate all A and B rows that are not referenced in a C row.
You can use NOT EXISTS
:
SELECT A.aID, B.bID
FROM A INNER JOIN B
ON A.aID = B.aID
WHERE NOT EXISTS
(
SELECT 1 FROM C
WHERE A.aID = C.aID OR B.bID = C.bID
)
Not Exists
is the preferred way since it has no issues with null values on the join-column. Read.
If not every a-row has a b-row but you want to see the a-row anyway(as mentioned in a comment to the other answer), you have to use an Outer Join
:
SELECT A.aID, B.bID
FROM A LEFT OUTER JOIN B
ON A.aID = B.aID
WHERE NOT EXISTS ... (rest same)
Upvotes: 3
Reputation: 727137
You cab do it like this:
SELECT A.aID, B.bID
FROM A
LEFT OUTER JOIN B ON A.aID=B.aID -- We are not guaranteed to have B for each A
LEFT OUTER JOIN C ON C.aID=A.aID AND (C.bID IS NULL OR C.bID=B.bID)
WHERE C.cID IS NULL
Note: Edited in response to a comment.
Upvotes: 1