Get all IDs that do not associate with a specific parent ID-CodePudding

There is a specific child/parent table structure in my DB: CHILD_TABLE:

| child_table |
|-------------|
| id          |
| node_id     |

A PARENT_TABLE:

| parent_table |
|--------------|
| id           |
| node_id      |

and an ASSOCIATION_TABLE:

| association_table |
|-------------------|
| parent_node       |
| child_node        |

(ManyToOne on both parent and child tables)

Let's say we load them with test data as:

-- child table

| id | node_id |
|----|---------|
| 1  | 1       |
| 2  | 2       |
| 3  | 3       |

-- parent table

| id | node_id |
|----|---------|
| 1  | 1       |
| 2  | 2       |
| 3  | 3       |
| 4  | 4       |

-- association table

| parent_id | child_id |
|-----------|----------|
| 1         | 1        |
| 2         | 1        |
| 2         | 2        |
| 3         | 3        |
| 4         | 1        |

Given a list of parent IDs and a single parent ID, I want to find all child IDs that are associated with those IDs but not the single one.

In the example data above,

List of parent IDs : (1, 2)
Single parent ID: 4

The result should be child.id = 2 because that entry has no connection with parent.id = 4 but there is at least one connection with the given "parent IDs".

EDIT

I managed to get something working with by subtracting one result over the other:

SELECT child.id
FROM child_table child
WHERE child.node_id
IN (
  SELECT assoc.child_node
  FROM association_table assoc
  WHERE assoc.parent_node
  IN (
    SELECT parent.node_id
    FROM parent_table parent
    WHERE parent.id IN (1, 2)
  )
)

MINUS

SELECT child2.id
FROM child_table child2
WHERE child2.node_id
IN (
  SELECT assoc2.child_node
  FROM association_table assoc2
  WHERE assoc2.parent_node
  IN (
    SELECT parent2.node_id
    FROM parent_table parent2
    WHERE parent2.id = 4
  )
);

Is there an alternative/simpler way of doing the same thing?

CodePudding user response：

You just need the association table. Select from it all children for the given parent list, from there use NOT EXISTS to remove all child associations with the single parent id. (see demo)

select a1.child_id 
  from association a1 
 where a1.parent_id in (1,2) 
   and not exists ( select null 
                      from association a2 
                     where a1.child_id = a2.child_id 
                       and a2.parent_id = 4
                   );