Máté Juhász Máté Juhász - 2 months ago 6
SQL Question

Finding items in child table referencing records in master table not referenced by other children

I've a database with two table

MasterTable
and
ChildrenTable
, there is a one-to-many relationship between them. (of course this is only part of the database)

I need to find records in
ChildrenTable
which are the only records referencing item in master table. (e.g. I need to find Child1 if that's the only child linked to Master1, but not to find Child2 if also Child3 is linked to Master2).

I know I could've done it with a subquery too, but I thought this other approach would be easier:

SELECT
MasterTable.Name,
ChildrenTable.Name
FROM
MasterTable INNER JOIN ChildrenTable
ON MasterTable.ID = ChildrenTable.MasterID
LEFT JOIN ChildrenTable ChildrenTable1
ON MasterTable.ID = ChildrenTable1.MasterID
WHERE
ChildrenTable.Name = 'SomeName'
AND ChildrenTable.ID <> NVL(ChildrenTable1.ID,0)
AND ChildrenTable1.ID Is Null;


But this query doesn't give me any results. When I exclude last condition I get results, but only those where
ChildrenTable1.ID
is not null (I've checked the data and there are records should be found.)

How can I fix this?

Answer

Here's SQL Server syntax. Not sure if it translates directly to Oracle's dialect:

SELECT MAX(ID), MasterID
FROM ChildrenTable
WHERE MasterID IS NOT NULL
GROUP BY MasterID
HAVING COUNT(1) = 1

If children can exist in different tables, an option might be to UNION them:

SELECT MAX(u.ID), u.MasterID
FROM 
(
 SELECT c1.ID, c1.MasterID
 FROM ChildrenTable c1
 WHERE c1.MasterID IS NOT NULL
 UNION ALL
 SELECT c2.ID, c2.MasterID
 FROM SomeOtherChildTable c2
 WHERE c2.MasterID IS NOT NULL
) u
GROUP BY u.MasterID
HAVING COUNT(1) = 1