Your method is correct. The only issue is if you are processing huge amount
of data, this will take some (may be lot of) time. You can use not like
option for "DO NOT contain" query.
...
where title not like '%word%'
rgds
amar
-----Original Message-----
Sent: Tuesday, August 21, 2001 5:06 PM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Amar Kumar Padhi
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
