Hi !

My problem is that:

I have many databases, and many of them need to search in masters.
Masters can be everything: articles, books, persons or photos.
Every masters have detail datas, example: books have editors, authors,
etc.
I have code tables (lookups).
Example:
Book (Master), Book-Authors (Detail), Author (Code Table).

So: I need to find Books quickly by all possible fields (with details !).
The problem is: how to I separate the searching result, and the showed
searching result ?
(In searching I don't need some tables or fields, but need subdetails.
In result showing I don't need subdetails, but I need code tables, and
fields.
Example: Book - Book-Authors/Book-Editors (for search) - Book
Format/Book Publisher fks/lookups in result.


In DBISAM I can do that:
select MASTER_ID from A where .... to MEM_A;
select MASTER_ID from B where .... to MEM_B;
select * from master, CODE1, CODE2, MEM_A, MEM_B where
(ID=MEM_A.MASTER_ID) and (ID=MEM_B.MASTER_ID)
and ... (CODE contacts) ...

This example is show, how I need to work.
I want to find every Master (Book) that user specify. Example: he/she
is want to see the books are created before 1982, and created by XY,
and Z, and keyword is "matematics".

1.)
First I try with dynamic queries with static in ID-s.
select MASTER_ID from A where .... to MEM_A; -> to IBQuery -> to
TStringList
select MASTER_ID from B where .... to MEM_B; -> to IBQuery -> to
TStringList

merge StringLists, distinct same.

select * from master,code1,... codeN where ID in (%s) <- StringList ID-s.

But IN is very slow - I see, and I don't believe, but that is true !

1.B)
select * from MASTER
where
ID in (select Master_ID from Detail1 where .....) and
ID in (select Master_ID from Detail2 where .....) and

But that is slow too.

2.)
Next I try with join, or where+contact table joins, but that is
generate a result with repeated Masters, because I in more detail
table have been filtered, it get more result.
If I distinct it, then I must join code tables again to show the good
result (what can showed in a DbGrid, or etc.).

3.)
Then I create a TEMPMASTER table with this structure:
- MASTER_ID (int) - any master id
- SESSION_ID (int) - every search get a new session id. After search
we delete records from this table with actual session id.
- ADDEDAT (DateTime) - insertion date. If records are not deleted
(example: program crashing), I delete every record that older than 3 day.

After the search I insert the distincted master ID-s to this table
with next session id.
Then I join the TEMPMASTER and MASTER tables in where, and I can get
the result, that joinable with code tables, etc, and reproducable.

I use this method in every database.
But it is complicated, and if I must create ReadOnly database (in CD),
I cannot search on, because I need to write this table (TEMPMASTER)
for better search.

So: how to separate the searching and the result showing, and how to I
create a better search method without TEMP table ?
Anybody have an IDEA ?

Thanx for it:
ft