CODEWITHSUNDEEP
sqlpostgresql
Uriziel
Uriziel

Reputation: 742

Fetch filtered tree like relation

I've got relation between 3 tables, let's call them tree, parent and children.

I want to (always) fetch tree, fetch parent if it's not deleted or has undeleted children and children if they're not deleted.

Following query works with exception that it doesn't fetch the tree.

SELECT * 
FROM tree t
LEFT JOIN parent p ON t.id = p.tree_id
LEFT JOIN children c ON p.id = c.parent_id
WHERE t.id = 2599 
    AND (p.deleted = false OR (p.deleted = true AND pc.deleted = false));

Any ideas?

Upvotes: 0

Views: 61

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

It is actually not that difficult. (EDIT: I wish I had never said this ;-) Only make sure to treat outer joined records correctly (i.e. check for null).

SELECT * 
FROM tree t
LEFT JOIN parent p ON t.id = p.tree_id
LEFT JOIN children c ON p.id = c.parent_id and c.deleted = false 
WHERE t.id = 2599 
AND (p.id is null OR p.deleted = false OR c.deleted = false) -- parent okay?
AND (c.id is null OR c.deleted = false) -- child okay?

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

As mentioned in the comments, it looks like you're got a problem in the second join.

Also, your WHERE clause is going to turn those LEFT JOINs in to INNER JOINs:
- NULL is never equal to either TRUE or FALSE

I'd try something like this...

SELECT
  * 
FROM
  tree       AS t
LEFT JOIN
  (
    parent     AS p
  LEFT JOIN
    children   AS c
      ON  c.parent_id = p.id
      AND c.deleted   = false
  )
    ON  (t.id = p.tree_id)
    AND (c.id IS NOT NULL OR p.deleted = false)
WHERE
  t.id = 2599

children only get associated to parent is they're not deleted.

That join only gets associated to tree if parent is not deleted, or there are children (we've already ensured that they're only included if they're not deleted).

EDIT :

I never like using RIGHT JOIN, but perhaps it could be more readable with it? I don't think so, but I'll include it for completeness.

SELECT
  * 
FROM
  parent     AS p
LEFT JOIN
  children   AS c
    ON  c.parent_id = p.id
    AND c.deleted   = false
RIGHT JOIN
  tree       AS t
    ON  (t.id = p.tree_id)
    AND (c.id IS NOT NULL OR p.deleted = false)
WHERE
  t.id = 2599

Upvotes: 1

Related Questions