SQL ignore inner query if it returns nothing-CodePudding

I have this table below where I keep history of order validation:

I want to select all records after the rejected status if the rejected status exists.

I have tried this query:

select * 
from status_timeline
where validation_date > (select max(validation_date) 
                         from status_timeline 
                         where order = 1345 and status = 'REJECT')
  and order = 111;

It works as expected only when there's a status REJECT, I want to ignore the subquery if it returns nothing

Thanks in advance.

CodePudding user response：

You can use coalesce here

select * 
from status_timeline
where validation_date >
 coalesce(
   (select max(validation_date) from status_timeline where order = 1345 and status = 'REJECT'),
   '19700101'
 ) and order = 111;

CodePudding user response：

I'd do a NOT EXISTS query.

select * 
from status_timeline st1
where not exists (select 1
                  from status_timeline st2
                  where st2.order = 1345 and st2.status = 'REJECT'
                    and st2.validation_date > st1.validation_date)
  and order = 111;

Or do a LEFT JOIN:

select st1.*
from status_timeline st1
left join status_timeline st2
    on  st2.order = 1345 and st2.status = 'REJECT'
    and st2.validation_date > st1.validation_date
where st1.order = 111
  and st2.validation_date is null;