At 11:21 AM 23-02-01 +0100, you wrote:

>Hi,
>
>what is a lookup link?

It's a client-side concept - there's no such thing especially implemented in the database engine.

Typically, it's a small table of relatively static values ( such as a Currency, Country or Account Type table) that has a small, atomic primary key and a piece of text associated with it. Sometimes these tables are referred to as "control tables". Other tables "look it up" by storing its primary key as a "lookup link".

To get the value into an output set, you can do a join on the lookup link and the control table's PK and ask for the "Description" column, e.g.

Select c.CustomerID, c.FirstName, c.Last Name, p.ProviderName
from Customer c join Provider on c.ProviderID = p.ProviderID
where.....

or like this:

Select c.CustomerID, c.FirstName, c.Last Name,
(select ProviderName from Provider where ProviderID = c.ProviderID)
as ProviderName
from Customer
where.....

>What are the advantages / disadvantage?

The join syntax might be faster in some cases than the subselect.
Tip - don't make foreign keys on "lookup links" because InterBase will create an index for it that has very low selectivity. Instead, handle any ref. integrity yourself manually, by way of triggers.

When you make an index for a lookup column, make a composite key of it, using the PK as a "partner", e.g. in the above example,

create index ixProvider on Customer(ProviderID, CustomerID

>How do I create a lookup link?

Like that. :)) I see you are an ibobjects user - that's good - IBO can do some very smart stuff with lookups.

Cheers,
Helen

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