How do I select only records which are referenced by their children in other tables?

Question

Not sure if the question is phrased correctly but here's an example of the schema:

CREATE TABLE TabA (Id INT NOT NULL, PRIMARY KEY (Id));

CREATE TABLE TabB (
    Id INT NOT NULL, 
    TabAId INT, 
    PRIMARY KEY (Id),
    FOREIGN KEY (TabAId) REFERENCES TabA(Id)
)

CREATE TABLE TabC (
    Id INT NOT NULL, 
    TabAId INT, 
    PRIMARY KEY (Id),
    FOREIGN KEY (TabAId) REFERENCES TabA(Id)
)

What I need to do is to select TabA.Id where TabA.Id is either referenced by records in TabB or TabC (or both). If I were to translate this programmatically:

type B = { Id : int }

type C = { Id : int }

type A = { ItemsB : B list ; ItemsC : C list }

let itemsA = [ ... ] // a list of items A
let filtered = itemsA |> List.filter (fun a -> a.ItemsB.Length > 0 || a.ItemsC.Length > 0)

So far I tried this query, but I'm not sure if it's good:

SELECT a.Id 
FROM TabA a
INNER JOIN
    (
        SELECT b.TabAId AS tabAId
        FROM TabB b
        FULL OUTER JOIN TabC c ON b.TabAId = c.TabAId
    ) sub
ON a.Id = sub.tabAId

Any thoughts? Thank you.

Answer 1

Why so complicated?

SELECT a.Id 
FROM TabA a
WHERE exists (select * from TabB b where b.tabAId = a.Id)
   OR exists (select * from TabC c where c.tabAId = a.Id)
ORDER BY ...;

You don't need DISTINCT here - a.Id is the primary key. In your original query, you could have used UNION instead of FULL OUTER JOIN. The FULL OUTER JOIN is nonsense - something you can see if you test just that part of the query and examine the resultset. In this case, UNION rather than UNION ALL is desired to remove duplicates. Generally speaking, most uses of UNION should be UNION ALL.

When you run into problems with a query that does not work, stop trying to fix it all at once. Break out the pieces and examine what they do. Often you will see problems that can be solved and might lead you to the desired logic.

To follow on with UNION comments, you could use:

SELECT b.TabAId AS Id FROM TabB as b
UNION 
SELECT c.TabAId FROM TabC as c
;

No need to reference TabA at all.

Answer 2

Here's one way to achieve it:

SELECT DISTINCT A.Id
FROM TabA A 
    LEFT JOIN TabB B ON A.Id = B.TabAId
    LEFT JOIN TabC C ON A.Id = C.TabAId
WHERE B.TabAId IS NOT NULL
    OR C.TabAId IS NOT NULL;

You can check the db<>fiddle.