RE: SQL Question

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Tue, 21 Aug 2001 07:00:41 -0700
Message-ID: <F001.003721E0.20010821061059@fatcity.com>

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: 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: Amar Kumar Padhi
INET: TS2017_at_emirates.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------