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: [EMAIL PROTECTED]
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: [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).
