Hi,

I am not very much experianced in SQL so I expect your sugestion and help.

I have two tables - "Customer" and "Orders" with relation ONE-to-MANY, so one Customer entry may have a few Order entries.

In Order table there is a Memo field for search with TextSearch function.

I have tried to run the SQL query below and everything works fine except that I get Canned result set. I would like to get Live.

Is it posible? I would like to show entries of first table in Live and perform search in second table.

If no, what could be other solutions?

SELECT DISTINCT Cus.Name from Cus JOIN Order ON Cus.ID=Order.ID
where TextSearch('Text to search' in Order.OrderMemo)


I use  DBISAM 4.x

Mike

Mike,

<< I am not very much experianced in SQL so I expect your sugestion and
help.

I have two tables - "Customer" and "Orders" with relation ONE-to-MANY, so
one Customer entry may have a few Order entries.

In Order table there is a Memo field for search with TextSearch function.

I have tried to run the SQL query below and everything works fine except
that I get Canned result set. I would like to get Live.

Is it posible? >>

Not in the same query.  However, you can link two separate queries using the
TDBISAMQuery.DataSource property and you will have live access to both:

http://www.elevatesoft.com/dbisam4d5_tdbisamquery_datasource.htm

Just set up the queries like this:

Customer:

SELECT * FROM Cus

Orders:

SELECT * from Order
where ID=:ID AND
TextSearch('Text to search' in OrderMemo)

--
Tim Young
Elevate Software
www.elevatesoft.com

T>>Not in the same query.  However, you can link two separate queries using the
T>>TDBISAMQuery.DataSource property and you will have live access to both:
T>>
T>>http://www.elevatesoft.com/dbisam4d5_tdbisamquery_datasource.htm
T>>
T>>Just set up the queries like this:
T>>
T>>Customer:
T>>
T>>SELECT * FROM Cus
T>>
T>>Orders:
T>>
T>>SELECT * from Order
T>>where ID=:ID AND
T>>TextSearch('Text to search' in OrderMemo)

What DataSource to which TDBISAM query I should connect?
I don't thing that I get result which I need using your example, as I understand
I will get Master/Detail relationship.
It is not good, I need to display all records in Cus table with IDs which are found in Order
table in Live mode.

The prototype in SQL could look like as  SQL below and it works, unfortunatelly only as Canned

select *
from Cus
WHERE Cus.ID in (SELECT Order.ID FROM Order WHERE TextSearch('Text to search' in OrderMemo))

Regards,
Mike

Mike,

<< What DataSource to which TDBISAM query I should connect?  I don't thing
that I get result which I need using your example, as I understand I will
get Master/Detail relationship. It is not good, I need to display all
records in Cus table with IDs which are found in Order
table in Live mode. >>

That's not possible with DBISAM without using TClientDataSet and cached
updates.

--
Tim Young
Elevate Software
www.elevatesoft.com