0

In case table be as the row (x=1,y=1) and f has the rows ((x=1, y=1), (x=1, y=2))

I want to achieve one result row only, since the first condition is met, the second ought to be ommited.

Select * from be join f        
ON ( (be.x = f.x AND f.y = b.y) OR (be.x = f.x))
Lokomotywa
  • 2,624
  • 8
  • 44
  • 73
  • Because a query is a statement of intent and not the actual way the query runs, you can only guarantee an ordering by pre-materialising. SQL is not designed to short circuit evaluation . Queries such as yours are better stated as a UNION query with separate conditions. https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated – Mitch Wheat Sep 24 '21 at 06:08
  • This is not what the query is ment to look like in the end, if I can achive what I want with a union query, so be it, I just like to get an example. – Lokomotywa Sep 24 '21 at 06:11

2 Answers2

1

Looks like you want NOT EXISTS.

Select * 
from be 
join f on (be.x = f.x and f.y = be.y) 
       or (be.x = f.x and not exists (
            select 1 
            from f f2
            where be.x = f2.x and f2.y = be.y))
Serg
  • 22,285
  • 5
  • 21
  • 48
0

Found a solution myself:

select distinct on (be.x)
f.y = b.y as condition_met
* from be join f 
ON ( (be.x = f.x AND f.y = b.y) OR (be.x = f.x))
order by be.x, condition_met desc
Lokomotywa
  • 2,624
  • 8
  • 44
  • 73