moacirponti wrote:

>Ok. Thanks for your help. It is really what I want, it is very close
>to a book store.
>
>But now there is another question. You say that:
>
>
>
>>If what you want is what I imagine, on the client side,
>>I will query with the above statment, to get all
>>relevant projects, and for each project I will list
>>EVERY author and EVERY KeyWord.
>>
>>
>
>How can I list every author an every keyword for "distinct" projects?
>I'm working on Delphi 6 + Firebird 1.5. I'm using a dbGRID to show
>the result of the query, and it is difficult to show all the authors
>and keywords for each project. How can I do this?
>
>Can I do it on a QuickReport? I'm trying to use Detail band (with
>information about PROJECTS)+ Subdetail (with information about
>Authors), but it not retuning any author or keyword data.
>
>With your help now I'm listing each project only once in report, but
>how can I list below the authors and keywords associated to the
>project?
>
>Thanks a lot,
>
>Moacir
>
>
>

Moacir,

This will be Off-Topic here...

What about if you have a dbgrid for the Project and two ListBoxes (one
for authors and another for KeyWords) ?

Once you get the projects related to the search criteria
Send two other queries and populate Authors and Keywords

Select * from Authors where pr_code = :ProjectCode
Select * from KeyWords where pr_code = :ProjectCode

Will do the job...

You could use the proposed query (the one that returns only the
projects) in a sub query that joins everything, I think you should
measure it up to see wich solution has better performance.

Try this:

Select
P.Code, P.Title, A.Name, K.Word
from
Project P join
Author A on (A.PRCode = P.Code) join
Keyword K on (K.PrCode = P.Code)
where
P.Code in (
SELECT distinct
P.CODE
from
PROJECT P join
AUTHOR A on (A.PRCODE = P.CODE) join
KEYWORD K on (K.PRCODE = P.CODE)
WHERE
(A.NAME like '%Info%' or K.WORD like '%Info%' or P.TITLE like '%Info%')
)

Will return evry project and every Author related to that project and
every keyword related to the project where the Author, or teh KeyWord or
the Project Title contains "Info"

You could create a table like this

Create table RelatedWords(
PRCode integer,
Word varchar(40));

And maintain this table in sync with the Project Titles, Authors Names
and Keywords (with triggers on the "Master" Tables)

Then you could query with this

Select
P.Code, P.Title, A.Name, K.Word
from
Project P join
Author A on (A.PRCode = P.Code) join
Keyword K on (K.PrCode = P.Code)
where
P.Code in (Select PRCode from RelatedWords where Word like '%Info%')

You should try with real data to see how each method perform.

See You !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br