postgresql how to check by two columns from join-CodePudding

There are some points and point_types with mark actual:

points
------
id  point_type
------
1   1
2   1
3   2

point_types
------
id  actual
------
1   true
2   false

Also there are directions:

directions
------
id  point_from  point_to
------
1   1           1
2   1           2
3   1           3
4   2           1
5   2           2
6   2           3
7   3           1
8   3           2
9   3           3

I need only directions with actual point_from and point_to:

id  point_from  point_to
------
1   1           1
2   1           2
4   2           1
5   2           2

Trying with:

SELECT d.id, d.point_from, d.point_to
FROM directions d 
JOIN points p ON (d.point_from = p.id OR d.point_to = p.id)
JOIN point_types pt ON pt.id = p.TYPE AND pt.actual = TRUE
GROUP BY 1,2,3

but getting directions with actual point_from or actual point_to :

id  point_from  point_to
------
1   1           1
2   1           2
3   1           3
4   2           1
5   2           2
6   2           3
7   3           1
8   3           2

CodePudding user response：

Use a CTE that returns all the ids of the not actual points and then with NOT EXISTS get only the directions that do not contain any of them:

WITH cte AS (
  SELECT p.id 
  FROM points p INNER JOIN point_types pt 
  ON pt.id = p.point_type
  WHERE pt.actual = false
)
SELECT d.*
FROM directions d
WHERE NOT EXISTS (SELECT * FROM cte c WHERE c.id IN (d.point_from, d.point_to));

See the demo.