At 12:27 PM 03-07-01 +0200, you wrote:

[problem snipped]

and solved by Svein...

>select p.*, s.* from patients p left outer join patientstatus s
>on ((p.id=s.id) and (p.name=s.name)) order by patients.name
>
>and you should be fine.

but...I observe the potential for chaos here, with this composite key in a 1:1 relationship. What happens if the admitting staff enter patient 101 as "Mary Ann Smith" into Patients and the clinical staff enter patient 101 as "Maryann Smith" into PatientStatus? With this key structure, the two will never match up. (Two RDBMS thunderclouds: non-atomic keys and redundancy!)

Why is the relationship not formed with the patient id alone?

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________