At 06:34 AM 12/03/2006, you wrote:

>I'm still not understanding key links very well. Please let me go through
>an example.
>
>BTW, I'm following Helen's advice on converting WHERE clause joins with
>explicit joins when I come across them. I had mainly used where clause
>joins to this point.
>
>Example:
>
>SELECT
> A.AppointmentTime,
> P.LastName,
> P.LastName || ', ' || P.FirstName /*|| ' ' || P.MiddleInitial*/
>PatientName,
> P.ChartID,
> P.HomePhone,
> 'Dr. ' || D.LastName DoctorName,
> A.Notes,
> APT.AUTOCOLOR
>FROM
> Appointment A
> INNER JOIN PATIENT P ON (A.PATIENTID = P.PATIENTID)
> INNER JOIN DOCTOR D ON (A.DOCTORID = D.DOCTORID)
> LEFT JOIN APPOINTMENTTYPE APT ON (A.TYPEDESCRIPTION = APT.TYPEDESCRIPTION)
>WHERE
> A.AppointmentDate = :AppointmentDate
>ORDER BY
> A.AppointmentTime,
> P.LastName
>
>In this query there are no primary keys in the select. So I should use the
>actual primary keys for the table?

Yes. At least, for Appointment, Doctor and Patient. For
AppointmentType you've got a potential problem because of the left
join (nulls) so use a correlated subquery instead for that.

>So KeyLinks whould be:

> APPOINTMENT.APPOINTMENTID

>That would uniquely identify each record, but Helen said I need ALL of the
>primary keys of the underlying table so would my keylinks then be:
>
>APPOINTMENT.APPOINTMENTID
>PATIENT.PATIENTID
>DOCTOR.DOCTORID
>APPOINTMENTTYPE.TYPEDESCRIPTION

Replace the join to AppointmentType with a subquery and include the
PatientID and the DoctorID. However, Appointment is actually an
intersection of the other two tables and if apt.PatientID and
apt.DoctorID are constrained with a UNIQUE constraint, there cannot
be duplicated keys in the output of this query.

(However, if your system really is a clinic calendar, the
AppointmentID alone won't do it. Fertility clinics, for example,
usually make appointments for two patients to see one or more
doctors....so there's a missing level of intersection in your structure...)

So, if you don't need to update, add or delete any Doctor or Patient
records from this set, you will probably be OK with
appointment.AppointmentID alone.

>Those are my primary keys on those tables.
>
>In the KeyLinks demo program there was something like:
>
>CUSTOMER.ORDERID = ORDER.ORDERID
>
>I'm not sure I understand what this means as the query was a simple select
>(select * from customer) in the demo app.

This one is a totally different thing. KeyLinks (which are stored in
the KeyFields structures for all sets) drive all DML (I'll explain in
a moment); this dual Keylinks entry, viz. tableb.keylink=tablea.key,
represents a specific type of relationship in the application. You
will bump into this later, when you come to use embedded
ib_lookupcombo. For basic understanding of datasets, you can bypass
it for now.

>Also I still don't understand why this is necessary because it's working
>without the key links?

The Select will work but updates, inserts, deletes, filters and
searches won't. As your "decider" for Keylinks in joined sets, it
must be possible for all DMLs and searches to construct a
parameterised WHERE criterion from a KeyLink for each underlying
table row that is to be targeted for a positioned update. In your
query, that's not possible because you have nothing there that is a
candidate for a KeyLink. Derived fields (like your PatientName and
DoctorName) can't be KeyLinks because they don't exist in the tables.

Helen