| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: vertical serches on a table - how to
Stephane,
Fantastic! Appreciate the help.
Thanks!
susan
>From: "Stephane Faroult" <sfaroult_at_oriolecorp.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: sfaroult_at_oriolecorp.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).
-- 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 - 13:44:28 CDT
 |
 |