Helen,

thanks for your concerns. the design of the client app enforces the 1:1 by
allowing the user to select a patient and then adding the additional data if
required. the problem with not using ID alone is a quirk of the uk nhs. this
equipment is for testing neonates and at the moment there is no unique id
issued at birth, each hospital has it's own numbering system until the nhs
number is issued. because our kit can accumulate data from multiple sites
the name/id seemed necessary. i think i should have used a guid as a hidden
unique-id.

anyway, back to the question...

i did try the 'order by patients.name' (and p.name) and both situations
report no column of this name, so no fix yet!

i also tried listing the columns explicitly in the select but got the same
error.

Kind regards,
Pete

> -----Original Message-----
> From: Helen Borrie [mailto:helebor@...]
> Sent: 03 July 2001 13:15
> To: ib-support@yahoogroups.com
> Subject: Re: [ib-support] join-sort question
>
>
> 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