How do I modify a SQL JOIN with an OR operator such that the second condition only gets evaluated wh-CodePudding

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))

CodePudding user response：

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))

CodePudding user response：

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