RE: SQL Question

Bill

You can do this with intermedia text, but I wouldn't recommend it unless: a) your tables are big; b) you need to do this all the time; and c) doing it the way you suggested is too slow.

Essentially, this involves creating an IMT index on biblio.title that actually indexes biblio.title, subject.subject and keyword.keyword, all concatenated together - see the IMT Reference -> 3 Indexing -> Datastore Objects -> USER_DATASTORE for details and an example.

Regards
David Lord

> -----Original Message-----
> From: Bill Tantzen [mailto:tantz001_at_tc.umn.edu]
> Sent: 21 August 2001 14:06
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Question
>
>
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lord, David - C&S
  INET: David.Lord_at_hayscsg.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).