Svein Erling Tysvær wrote:

>
> Try changing to
>
> create view v_a as
> select a.id, a.mgrid, b.empid as SempTestUniqueName
> from t_a a
> left outer join t_c c on a.mgrid = c.mgrid
> join t_b b on b.empid = c.empid
>

Here I think I disagree with Set, for a slightly obscure reason. The
order of join processing in this case will be a to c (as an outer join,
preserving a) then that result to b using a vale from c, not preserving
anything. You won't see any a's that don't have matching b's and c's ...


I think you need some parenthesis to make this work.


create view v_a as
select a.id, a.mgrid, b.empid as SempTestUniqueName
from t_a a
left outer join (t_c c join t_b b on b.empid = c.empid)
on a.mgrid = c.mgrid

This builds a stream of matching b and c records and performs an outer
join between a and that stream, preserving a.

But I still think there's an undiscovered ambiguity in your original query.

Regards,


Ann