Helen Borrie wrote:

>>
>>select * from TABLE1 a
>>left join ( select * from TABLE2 a2 where a2."Val" = :Val and a2."Pos"
>>= :Pos ) b
>>on a."ID" = b."ID_ref"
>>
>
>
> Well, the derived table seems redundant for this example, which appears to
> be a straightforward left join.
>
> select a.*, a2.*
> from TABLE1 a
> left join TABLE2 a2
> on a."ID" = a2."ID_ref"
> where a2."Val" = :Val
> and a2."Pos" = :Pos
>

My understanding is that putting the conditions

a2."Val" = :Val and a2."Pos" = :Pos

into the where clause changes the query from a left
join to an inner join, since missing values from the
right table won't pass the where sieve.

Putting the conditions into the "on" clause should
give the expected result for a left join.

select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a."ID" = a2."ID_ref" and
a2."Val" = :Val and
a2."Pos" = :Pos

And, of course, your life would be better if you didn't define fields
with double quotes so you can write the query like this:

select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a.ID = a2.ID_ref and
a2.Val = :Val and
a2.Pos = :Pos


Regards,


Ann