Re: SQL Question

Bill,

How about inserting all the words from title and subject into the keywords table? You can do this using triggers on those tables.

Second, maintain your keyword table so that each keyword record contains the id and a single word, not groups of words. Do this with triggers too, on the keyword table.

Then, you can index the keyword table, get rid of the % signs around the query so that it'll use the index, query only the keyword table, and you can query for both existence or non-existence within the keyword table.

hth,

Yosi

Bill Tantzen wrote:

> Greetings!
>
> I don't see a lot of general sql questions on this list, so if this is the
> wrong place to post this sort of thing, just let me know! This will
> hopefully seem like a simple question, but I am a relative novice in sql
> programming!
>
> Here is the (simplified) scenario with three tables:
>
> biblio table
> id integer
> isbn varchar2
> title varchar2
>
> subject table (0 or many per id)
> id integer (fk biblio.id)
> subject varchar2
>
> keyword table (0 or many per id)
> id integer (fk biblio.id)
> keyword varchar2
>
> I wish to find all the id's that contain a given word in any of the varchar
> fields. My approach has been something like:
>
> select id from biblio
> where title like '%word%'
> union
> select id from subject
> where subject like '%word%'
> union
> select id from keyword
> where keyword like '%word%'
>
> First question: do you think this is a good way to do it? Is there a
> better way?
> Second question, how do I do a negative search, that is, find all the id's
> that DO NOT contain a given word in any of the varchar fields. Using an
> approach similar to the previous sql (using intersect instead of union) does
> not work, since there may be biblio records that do not have corresponding
> subject or keyword records.
>
> Perhaps there is not a simple query that will do the trick and I should be
> using a stored procedure?
>
> Thanks in advance for any advice!!!!
> Bill
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bill Tantzen
> INET: tantz001_at_tc.umn.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Thanks,

Yosi


---------------------------------------------------------
Yosi Greenfield
Oracle Certified DBA
ygreenfield_at_compuserve.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yosi Greenfield
  INET: yosi_at_comhill.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).