SELECT Records From Child Table That Are Not In Another And Parent Has No Other Children

I’m trying to write a query to select all orphan records that are in a child table, but only when orphaned records are the only items in the table.

I have two separate databases from separate systems, and so I cannot enforce referential integrity constraints using foreign keys. A simple schema of each is below:

ITEMS
Order  |  Item
----------------
1      |   A1
1      |   A2
2      |   A1

COMP
Item
-------
A2

Using the above data as an example, I’d want a query that returns Order 2 because the only item(s) available on the parent have been deleted.

So far, I have the below query that returns for me all records that are orphaned, including order 1.

SELECT Order FROM Items as i LEFT JOIN Comp as c ON i.Item = c.Item WHERE c.Item IS NULL

Any ideas?

Answer

Your query is almost there:

SELECT i.Order 
FROM 
  Items as i 
  LEFT JOIN Comp as c 
  ON i.Item = c.Item 
GROUP BY i.Order
HAVING COUNT(c.Item) = 0

this query finds all orders where every c.Item related to the order is null

after left joining your data looks like:

i.Order | i.Item | c.Item
1       |   A1   |  null
1       |   A2   |  A2
2       |   A1   |  null

grouping and counting c.Item gives;

i.Order | i.Item | COUNT(c.Item)
1       |   A1   |  1
2       |   A1   |  0

because count counts up, a step of 0 for nulls and increment of 1 for any non null

and we want the i.Order=2 row which is what HAVING gives us; HAVING is a where clause that is applied after grouping is done. (WHERE clauses are applied before grouping and cannot be used for this)

hence this query finds every order where ALL of its c.Items are NULL