Stephane, Apologize for not being clear on my question.
The query you have provided will only return one record, ie. 1 mango banana.
I need two records to be returned: 1 mango banana 1 grape pineapple
You're right that by 'vertical', I meant filtering according to conditions on OTHER rows but at the same time, I want those filter conditions to be displayed as well, which in this case - grape and pineapple.
Any idea.
Thanks.
susan
From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: vertical serches on a table - how to Date: Wed, 01 Oct 2003 06:14:32 -0800
>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 >
Susan,
Took me some time to understand what you meant by 'vertical search'. ANDs and ORs in a WHERE clause always apply to the current row under scrutiny. What you mean by 'vertical' is that you want to filter according to conditions on OTHER rows. This is done by a subquery.
Your query could read
select X1.ID, X1.col1, X1.col2 from tableX X1 where (X1.col1='banana') or ((X1.col1='mango' and X1.col2='banana') and EXISTS (select null from tableX X2 where X2.col1='grape' and X2.col2='pineapple' and X2.ID = X1.ID));
Simplifying to the extreme, each different row you handle must be returned by its 'own' query.
Regards,
Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
_________________________________________________________________
Frustrated with dial-up? Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com
-- 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).
