At 10:01 AM 12/06/2003 +0000, you wrote:

>key links: ins't this is use for identifying the primary key or
>unique key for the table?

While Keylinks on a simple select query is usually equivalent to the PK,
it's not the case in joined sets. The rule of thumb for Keylinks is that
it is the set of columns which together uniquely identify a row in the
output set (a.k.a the set of columns and rows that the query returns to the
dataset).

>Why is it I see it using it in other
>purposes like in TIB_lookupcombo? And I have seen an example like
>this table1.column1 = table2.colum1, table1.column2 =
>table2.column2 ... Iam confious...

Because the Keylinks structure provides the unique "key" for the output
set, it is available to IBO to identify and enforce the relationship
between one-and-only-one row in the lookup set and the lookup key in
one-or-more rows of the parent set. When you have a not-nil Keysource in a
dataset, IBO looks in Keylinks to find not just the lookup set's unique key
(the left-hand side of the Keylinks attribute) but also the
"=ParentLookupKey" on the right. The Keysource property points to the
ib_datasource's Dataset. The ParentLookupKey points to the column in the
parent dataset that is polling for the lookup value.

In other words, the "paired Keylinks" is particular to a dataset which has
a Keysource. Only a lookup set for use with a TIB_LookupCombo can use a
Keysource. You can't implement a master-detail relationship using
Keysource, since the Keysource-Lookup relationship is logically the
inversion of a master-detail relationship. So there will be a conflict if
you try to associate both the Keysource and the Mastersource properties of
the lookup set with the parent's Datasource.

You use the table specifiers to ensure that, when refreshing a lookup that
has been invalidated by selecting a different value, IBO will form the
right query to refresh the relationship. The IBO query parser picks up the
table specifiers in order to construct this query.

>if i have a join sql like this "select m.name, d.description,
>d2.description from employee m inner join employeetype d on m.type =
>d.type inner join department d2 on m.department = d2.department". how
>do i fill up the keylink?

The minimum Keylinks in any joined set will consist of the primary keys of
all of the tables involved. This is true even if you know yourself that
there is a 1:1 relationship in a join between two tables. IBO has no way
to know that.

Where you don't have all of PKs available in the set, you then have to find
an alternative combination that will guarantee uniqueness. There's no "IBO
rule" for this: it requires you to understand how your tables relate to
one another. If you can't find a unique combination for keylinks, it
usually means there is something illogical about your query spec.

>before using IB Objects ... I don't see this kind of sql
>statement ... "select name,(select description from employeetype d
>where m.type = d.type) from employee m". But now I see this kind of
>sql statement appear in several examples with IB Objects. Is that
>kind of sql statement the same from this kind ... "select m.name,
>d.description from employee m inner join employeetype d on m.type =
>d.type? If not how do they differ?

You're getting into the area of SQL logic here and it's not the job of IBO
to teach you SQL theory. You need to raise that sort of question in
ib-support. The first style of query uses what is called a correlated
subquery expression to output a column. Very often, the same output set
can be achieved with a join. The subquery approach is sometimes faster
than a join on sets that are tightly restricted by a WHERE clause. It's up
to you as the developer to test your queries with typical volumes of data
and work out which performs better.

>join links: it says that it is use when we have implicit join
>statement ... what if I have this kind of sql statement ... "select *
>from table1 inner join table2 on table1.column1 = table2.column2". Do
>I have to use the join links?

No. Implicit joins are an old syntax which you should avoid. The above
statement is illogical, but an example of implicit join syntax would be
select table1.*, table2.something
from table1, table2
where table1.column1 = table2.column2
and table1.PurchaseDate < cast ('today' as date)

Because this syntax puts the join criteria in the WHERE clause, along with
the search criteria, you'd use JoinLinks to tell IBO which of the criteria
in the clause are join criteria.

fwiw, the statement you need for your example above is

select * from table1 t1
where exists (select 1 from table2 t2
where t2.column2 = t1.column1)

i.e. here you have another use of a correlated subquery expression, this
time in the search predicate.

Another tip - the INNER and OUTER keywords are optional in Firebird join
specifications. If a join is LEFT, RIGHT or FULL it's always
OUTER; otherwise it's INNER.

Helen