Is there an easy way to select all referencing records of a 3rd or 4th relation where at least one of them (IN operator) matches?

Question

A code snippet will be the best example of what I'm trying to accomplish:

SELECT 
    A.*, B.*, C.* D.*
FROM 
    tableA A
JOIN 
    tableB B ON A.id = B.a_id
JOIN 
    tableC C ON B.id = C.b_id
JOIN 
    tableD D ON C.id = D.c_id
WHERE 
    EXISTS (SELECT 1
            FROM tableA A
            JOIN tableB B ON A.id = B.a_id
            JOIN tableC C ON B.id = C.b_id
            JOIN tableD D ON C.id = D.c_id
            WHERE D.column_name = 'desired_value');

I've created a DB fiddle: https://www.db-fiddle.com/f/jA8jHqjAZ7opTx5qHPzpev/1

I want to select all of the rows from A, B, C and D using left joins. I only want to select A's that have a distant related D record matching a given predicate. I want to select all of the D records for any A that matches, even if all but one of those D records match the predicate. Simply - at least one must meet the predicate, but we want to select all that reference A through the joins.

This has been simplified for the code example here. Potentially there could be predicates on B and C as well, but we'd still want all the Bs and Cs that are referencing A.

Is there is an easier way than repeating all the joins in a subquery, and having to use extra subqueries with more joins for additional predicates?

Answer 1

I may have found one possible solution (thank you ChatGPT). It seems to work but I'm not entirely sure if it's really what I'm looking for... I will know more after using it more but suggestions are appreciated.

SELECT a.*, b.*, c.*, d.*
FROM a
JOIN b ON a.id = b.a_id
JOIN c ON b.id = c.b_id
JOIN d ON c.id = d.c_id
WHERE EXISTS (
    SELECT 1
    FROM d
    WHERE d.c_id = c.id AND d.x > 5
);

DB Fiddle: https://www.db-fiddle.com/f/dMrSga1JEpUcLf7JxbqjDe/1

The d.c_id = c.id is key, without it the query will return any D record that is related to an A record, even if the predicate is unmet, because it will use literally any D record in the table. The d.c_id = c.id ensures that the D record used is one of those from the first level joins.