Left outer with criteria on the child

[OracleNovice]

I have a problem in that I need to supply a View for reuse, and the difficulty is that whenever I add criteria to the child side of the View, I don't get all of the parents.

Oracle 8

Tables:
Employee (parent)
empid name
1 J Smith
2 J Doe

Details (child)
empid Activitydate ActivityCode
1 1/1/80 1
1 1/1/83 2
2 1/1/70 0
2 1/1/70 1
2 1/1/70 2

I would like to have a View which allows for providing selects such as the maximum activity code and the associated date for a changing empid (this will be passed from a 3rd party tool).

select
A.empid,
B.Activitydate,
B.ActivityCode
where
A.Empid = B.Empid(+))
and
...

-Hebert

 

[Turkbear]

Hi,
Please clarify..

that whenever I add criteria to the child side of the View, I don't get all of the parents.


Do you mean that you do not get parents for those records in the child that match the added criteria OR that records from the parent table that do not match the child criteria
are not returned.