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