Oracle-L: vertical serches on a table

From: Susan Tay <susantay2_at_hotmail.com>
Date: Wed, 01 Oct 2003 05:29:29 -0800
Message-ID: <F001.005D1AEB.20031001052929@fatcity.com>

Hi,
I have a table that I would like to perform a vertical search on. For eg.

Table X

ID        COL1     COL2
1         apple     orange
1         mango   banana
1         grape     pineapple
2         mango   banana
2         guava     lemon

I would like to display records that meet the following criteria for *a particular ID*.

(COL1=banana)

OR
(COL1=mango and COL2=banana

AND
COL1=grape and COL2=pineapple

The output should be
ID COL1 COL2
1 mango banana
1 grape pineapple

It should not display
ID COL1 COL2
2 mango banana

since ID=2 did not meet the criteria where COL1=grape and COL2=pineapple.

I tried the following SQL but the output is always zero because COL1 can never be a mango and a grape and COL2 can never be a banana and a pineapple at the same time for a particular ID.

select ID, col1, col2
from tableX
where (col1='banana')

or     ((col1='mango' and col2='banana')
         and
        (col1='grape' and col2='pineapple')
        );

Any idea how I can do a vertical search on the table.

Thanks for any help you can provide.

susan

Help protect your PC. Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Susan Tay
  INET: susantay2_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Wed Oct 01 2003 - 08:29:29 CDT

vertical serches on a table - how to