RE: vertical serches on a table - how to

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