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 --- IDCOL1 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 COL1COL2 1mango banana 1grapepineapple It should not display ID COL1COL2 2mango 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 _ Help protect your PC. Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- 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).
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 mangobanana 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 COL1COL2 1mango banana 1grapepineapple It should not display ID COL1COL2 2mango 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 fromtableX 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 fromtableX 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).
RE: RE: vertical serches on a table - how to
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 mangobanana 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 COL1COL2 1 mango banana 1 grapepineapple It should not display ID COL1COL2 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).
