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,
>
> SELECT first 1 A.CODE, A.TITLE, B.NAME, C.WORD from PROJECT A,

AUTHORS B,

> KEYWORDS C
> WHERE (B.NAME like '%AnyString%' or C.WORD like '%AnyString%' or
> A.TITLE like '%AnyString%')
> AND B.PRCODE = A.CODE
> AND C.PRCODE = A.CODE
>
>
> see you !
>
> --
>
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda.
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br

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

>
> > An SQL problem!
> >
> > I have 3 tables: PROJECT, AUTHORS and KEYWORDS
> >
> > One Project have various AUTHORS and various KEYWORDS, this two
> >tables have the fields PRCODE, with the code of their Projects.
> >
> > I want to do a select that retrieves:
> >
> > One Record containing: PROJECT CODE, PROJECT TITLE, the first
> >AUTHOR.
> > This search can be made on fields: AUTHOR, TITLE or KEYWORDS
> >
> > That is my "select":
> >
> > SELECT A.CODE, A.TITLE, B.NAME, C.WORD from PROJECT A, AUTHORS

B,

> >KEYWORDS C
> > WHERE (B.NAME like %AnyString% or C.WORD like %AnyString% or
> >A.TITLE like %AnyString%)
> > AND B.PRCODE = A.CODE
> > AND C.PRCODE = A.CODE
> >
> > Example of the select above.
> > For a project with 2 authors and 3 keywords this is returning:
> >
> > 1 The-Project-Title First-Author First-Keyword
> > 1 The-Project-Title Second-Author First-Keyword
> > 1 The-Project-Title First-Author Second-Keyword
> > 1 The-Project-Title Second-Author Second-Keyword
> > 1 The-Project-Title First-Author Third-Keyword
> > 1 The-Project-Title Second-Author Third-Keyword
> >
> >
> > Why can I made a SELECT that retrieves one RECORD, containint

the

> >first author and first keyword only?
> >
> > Thanks a lot
> >
> > Moacir
> >
> >