RE: SQL Question

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 21 Aug 2001 06:55:08 -0700
Message-ID: <F001.00372396.20010821070531@fatcity.com>

Bill,

Your sql looks like it works for me. My only concern is that you will not know which of the tables contains the "word" you are looking for. But, since you are returning the id from all tables, and this column is a FK to the parent, you will at least find the parent record that will lead you to the result you want.

For the second question, try the opposite:

select id from biblio
where title not like '%word%'
union
select id from subject
where subject not like '%word%'
union
select id from keyword
where keyword not like '%word%'

Again, this is an un-coordinated query in that the child tables do not relate to the parent table in any way. Also, you will not get a result if a record exists in the parent table, but a record does not exist in one of the child tables. Is this ok?

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, August 21, 2001 9:06 AM
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: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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