What I’m trying to achieve with this query is: always do the “mother” conditional, but if there isn’t mother, then do the “dad” conditional, is this posible?
"select name from Person where idParents = :idParents and" + " CASE " + " WHEN idMother !=NULL THEN idMother = :idMother " + " ELSE idFather = :idFather " + " END";
Advertisement
Answer
This query:
select name from Person where idParents = :idParents and 1 = case when idMother = :idMother then 1 when idFather = :iidFather then 1 end
will try to join first by matching the column idMother
to :idMother
.
If this is not possible then a match of idFather
to :idFather
will be used, if it exists.
This query:
select name from Person where idParents = :idParents and 1 = case when idMother = :idMother then 1 when idMother is null and idFather = :idFather then 1 end
will try to join first by matching the column idMother
to :idMother
.
If this is not possible then, only if idMother
is null
, a match of idFather
to :idFather
will be used, if it exists.
Or maybe you want this:
select p.name from Person p where p.idParents = :idParents and ( p.idMother = :idMother or ( p.idFather = :idFather and not exists (select 1 from Person where idParents = p.idParents and idMother = :idMother) ) )