Subject RE: [IBO] Grid lookup
what is the difference between

SELECT a.Field,
(SELECT Title as The_Title
FROM Titles t WHERE t.TitleID = b.TitleID),
FROM (tables, joins)


SELECT a.Field,
t.Title as The_Title,
FROM (tables, joins + JOIN Titles t ON ....)

when using Lookup Combos. Why is recommended the first solution?

My experience was that I was unable to set up OrderingLinks and
OrderingItems correctly when using the first solution and there was an
error that the lookup colum does?nt exist.

The second solution works fine with no problems, ordering by the lookup
column too.

Ales Kahanek

-----Original Message-----
From: Helen Borrie (TeamIBO) [mailto:helebor@...]
Sent: Sunday, January 20, 2002 3:20 PM
Subject: Re: [IBO] Grid lookup

At 03:59 PM 20-01-02 -0800, you wrote:
>Hi all,
>Is it possible to have a lookup on a field in a "joined" table. For
>if MemberBen is the table representing the relationship between a
Member and
>his Beneficiaries, and titleid is the foriegn key in the Beneficiary
>how do you get a drop down in for eg a grid, of all possible titles
from the
>Title table. There seems to be a problem declaring KeyLinks for the

Lookup fields refer to columns, not tables (but see my NOTE, below) so
the join doesn't affect the ability to implement the lookup. Just don't
include the Title table in the join, though. Use a correlated subquery
to bring the Title text into the main query. Include TitleID in the
field list for the main query, just don't display it.

Mainquery would be

SELECT a.Field,
(SELECT Title as The_Title
FROM Titles t WHERE t.TitleID = b.TitleID),
FROM (tables, joins and wheres)

The lookup dataset is

SELECT TitleID, Title FROM Titles

KeyLinks of the Lookup dataset:

KeyDescLinks of the Lookup dataset:

Don't forget to link the lookup dataset's KeySource property to the
datasource of MainQuery.

Just make sure the joined query is squeaky-clean with respect to join
syntax and table identifiers.
Use JOIN, not the SQL-89 inner join syntax.

Note: If you've eliminated all possibility of ambiguity and yet find
you get problems using the aliases, then remove the aliases and
substitute with fully-qualified columns in the join statement. Do that
as a last resort, though. IBO should be able to peel back and do the
linking through the aliases.


Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at

IB Objects - direct, complete, custom connectivity to Firebird or
without the need for BDE, ODBC or any other layer.
___ - your IBO community resource for Tech Info
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to