Stephane, Fantastic! Appreciate the help.
Thanks!
susan
From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: RE: vertical serches on a table - how to Date: Wed, 01 Oct 2003 08:04:31 -0800
>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 >
In that case you keep something looking vaguely like the condition in your initial query but add a subquery to check that both conditions are satisfied :
select X1.ID, X1.col1, X1.col2 from tableX X1 where (X1.col1='banana') or (((X1.col1='mango' and X1.col2='banana') or (X1.col1='grape' and X1.col2='pineapple')) and 2 = (select count(*) from tableX X2 where ((X2.col1='mango' and X2.col2='banana') or (X2.col1='grape' and X2.col2='pineapple')) and X2.ID = X1.ID)));
No guarantee on the proper number of parentheses.
SF
>> >> >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).
