RE: RE: vertical serches on a table - how to

>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).