CODEWITHSUNDEEP
sqlsql-server
HTTP 501
HTTP 501

Reputation: 126

Joining tables with references to each other

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

Answers (2)

Tim Schmelter
Tim Schmelter

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

Sergey Kalinichenko
Sergey Kalinichenko

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

Related Questions