hi list,

can someone help with my sql (not MySQL !!, i'm strictly ib/fb) question
please. i have 2 tables which are related 1:1. The primary table links to
the 2nd table on 2 fields (name and id). the 2nd table only has records for
some of the records in the primary table. ie the basic patients details are
stored in the primary table, and additional optional information is stored
in the 2nd table. The 2nd table has a FK defined on (name,id) into the
primary table.

i need to extract data from both tables and sort on the name.

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

trouble is this returns the rows that have matching records in both tables
in order and then the records that only have only data in the primary table
in no particular order.

if i remove the order by clause the data is returned in a natural order with
the data from the 2nd table returned as and when it exists. what i need is
exactly this but ordered on name.

many thanks for your thoughts,

Kind regards,
Pete Bray
pete@...