Subject Re: [IBO] GSG099. TIB_SearchBar and TIB_SearchPanel/The Datamodule.
Author Jason Wharton
Once you get the concept behind this and it clicks, you will wonder why this
was so puzzling...

With one-to-one inner joins it isn't necessary to proliferate the primary
keys of all included tables. If you can pin it down to the single record in
the table in the FROM clause, and the JOIN's are not causing it to be
duplicated in the dataset, then you can simplify. Keep in mind that it is a
SINGLE record you are going for. This also implies that a single record can
only be in (or soley based upon a record within) a single table. If your
query is outside of this then you are to set your KeyLinks blank and set
KeyLinksAutoDefine to false.

When just grabbing values out of lookup tables here is how I like to do it
so I can avoid the possible JOIN ambiguities and keep a simple KeyLinks
setting. If your JOIN can be rewritten into the equivalent of this type of
query then you don't need the join'd table's primary key columns in the
KeyLinks property.

, p.productname
, ( select pt.Description from producttype pt where
p.producttype=pt.producttype ) as description
, ( select pc.productcolorid from productcolor pc where
pc.productid=p.productid ) as productcolorid
, ( select c.color from color c where c.colorcode=pc.colorcode ) as color
from product p

Then, the KeyLinks are only necessary from the PRODUCT table.

I've found this type of select statement tends to create friendlier plans
for optimization too.

Jason Wharton
CPS - Mesa AZ

-- We may not have it all together --
-- But together we have it all --

----- Original Message -----
From: "Raymond Kennington" <progsol@...>
To: <>
Sent: Wednesday, October 16, 2002 12:14 PM
Subject: Re: [IBO] GSG099. TIB_SearchBar and TIB_SearchPanel/The Datamodule.

> Jason Wharton wrote:
> >
> > This has already been explained. KeyLinks settings are necessary for
> > uniquely pulling a specific record from the server without the context
> > the query itself. That's the rule of thumb.
> To see if I understand this, I claim that 2 items in the KeyLinks for the
example can be
> removed without a change in effect. Is this so?
> This is the context of my question:
> The KeyLinks for this dataset look like this:
> Therefore PRODUCTTYPE.DESCRIPTION and COLOR.COLOR can be removed from the
KeyLinks as
> DESCRIPTION and COLOR are not required to define the record uniquely?

Yes, this is true.

> --
> Raymond Kennington
> Programming Solutions
> W2W Team B