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




--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@t...> wrote:

> moacirponti wrote:
>
> >Dear Friends
> >
> >The "FIRST 1" works when I have only one PROJECT found, but if the
> >select result in more projects, taking the "first 1" I throw away

the

> >other projects.
> >
> >Exemple:
> >SELECT A.CODE, A.TITLE, B.NAME, C.WORD from PROJECT A, AUTHORS B,
> >KEYWORDS C
> >WHERE (B.NAME like '%John%' or C.WORD like '%John%' or A.TITLE

like '%

> >John%')
> >AND B.PRCODE = A.CODE
> >AND C.PRCODE = A.CODE
> >
> >returns:
> >
> > 1 Statistical Procedures John A. Cusman Statistics
> > 1 Statistical Procedures Kelly C. Becare Statistics
> > 2 Retrieving Information John A. Cusman Data Bases
> > 2 Retrieving Information Marcelo G. Anton Data Bases
> > 2 Retrieving Information John A. Cusman Image Retrieval
> > 2 Retrieving Information Marcelo G. Anton Image Retrieval
> >
> >If I get the "first 1", the user cannot seen the project
> >2 "Retrieving Information"
> >
> >I need to do a SELECT to return only:
> > 1 Statistical Procedures John A. Cusman Statistics
> > 2 Retrieving Information John A. Cusman Data Bases
> >
> >Please help 'cause It's a very difficult problem for me.
> >
> >Thanks at all,
> >
> >Moacir.
> >
> >
> Moacir,
>
> I am trying to understand what you want...
>
> Do you want to list every project that has in the Project Name, or

in

> Authors Name or in Keywords, the word suplied by the user right ?
> Something like a Book Store ???
>
> If what you want is to know the projects that has "something" with

that

> "word" why list all the tables instead of just the project table ?
>
> Why show the keyword "Data Bases" and not the keyword "Image

Retrieval"

> since none of these words has something related with the search

crtiteria ?

>
> if you run this query:
>
> SELECT distinct
> P.CODE, P.TITLE
> from
> PROJECT P join
> AUTHOR A on (A.PRCODE = P.CODE) join
> KEYWORD K on (K.PRCODE = P.CODE)
> WHERE
> (A.NAME like '%John%' or K.WORD like '%John%' or P.TITLE like '%

John%')

>
> will return
> 1 Statistical Procedures
> 2 Retrieving Information
>
> if you run this one:
>
> SELECT distinct
> P.CODE, P.TITLE
> 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
> 2 Retrieving Information
>
> 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.

>
>
> Just an advice:
> 1.) Using like "%something%", will not use index, so don't expect a

screaming fast response if you have a lot of records on those tables.

>
> 2.) I note a normalization flaw on your model: Analizing the query

you propose, I note that the table authors will have an entry for
each author for each project. I supose your tables look like this:

>
> create table Project(Code integer, Title varchar(40));
> create table Author(Code integer, PrCode integer, Name varchar(40));
> create table Keyword(PrCode integer, Word varchar(40));
>
> and to populate it something like this:
> insert into project values (1, 'Statistical Procedures');
> insert into project values (2, 'Retrieving Information');
>
> insert into Author values (1, 1, 'John A. Cusman');
> insert into Author values (2, 1, 'Kelly C. Becare');
> insert into Author values (3, 2, 'John A. Cusman'); -- <- Problem

Here

> insert into Author values (4, 2, 'Marcelo G. Anton');
>
> insert into KeyWord values (1, 'Statistics');
> insert into KeyWord values (2, 'Data Bases');
> insert into KeyWord values (2, 'Image Retrieval');
>
>
> I would make it like this:
> create table Project(Code integer, Title varchar(40));
> create table Author(Code integer, Name varchar(40));
> create table ProjectAuthor(PrCode integer, AuCode integer);
> create table Keyword(PrCode integer, Word varchar(40));
>
> insert into project values (1, 'Statistical Procedures');
> insert into project values (2, 'Retrieving Information');
>
> insert into Author values (1, 'John A. Cusman');
> insert into Author values (2, 'Kelly C. Becare');
> insert into Author values (3, 'Marcelo G. Anton');
>
> insert into ProjectAuthor values (1, 1);
> insert into ProjectAuthor values (1, 2);
> insert into ProjectAuthor values (2, 1);
> insert into ProjectAuthor values (2, 3);
>
> insert into KeyWord values (1, 'Statistics');
> insert into KeyWord values (2, 'Data Bases');
> insert into KeyWord values (2, 'Image Retrieval');
>
>
> See you !
>
> --
>
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda.
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br