Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!t-online.de!news.germany.com!postnews.google.com!d57g2000hsg.googlegroups.com!not-for-mail From: "D" Newsgroups: comp.databases.oracle.server Subject: How to return records with mismatching criteria in the same column Date: 6 Apr 2007 10:26:05 -0700 Organization: http://groups.google.com Lines: 27 Message-ID: <1175880365.260684.90740@d57g2000hsg.googlegroups.com> NNTP-Posting-Host: 69.241.122.68 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1175880365 17428 127.0.0.1 (6 Apr 2007 17:26:05 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 6 Apr 2007 17:26:05 +0000 (UTC) User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: d57g2000hsg.googlegroups.com; posting-host=69.241.122.68; posting-account=awsIDQ0AAADn_Cq8gFjQrnPvcE2IKGCC Xref: news.f.de.plusline.net comp.databases.oracle.server:195891 Hello everyone. I have result set that I am pulling that shows a customer may have a different end date on multiple records. Is there a way to compare the values in the column and say if account number and customer and end date match, do not return values - but if account number and customer match but end date does not match all records for that account number and customer return all values for that customer? here is what I have so far and what I'm looking to do... SELECT /*+ first_rows(10) */ c.ACCTCORP, c.HOUSE, c.CUST, c.lname, c.fname, hsa.SVC_AVL_TYPE, cp.promocode, cp.stopdate FROM IDST_CUSTOMER c, IDST_HOUSE_SERVICE_AVLBLTY hsa, IDST_CUST_PROMO cp WHERE c.acctcorp=hsa.acctcorp AND c.house=hsa.house AND c.acctcorp=cp.acctcorp AND c.house=cp.house AND c.cust=cp.cust AND c.acctcorp=9518 AND hsa.svc_avl_type=59 AND cp.promocode IN ('! X','<3','<8','9','CM','CN','CS','CT','CY','H>','N<','O<','P<','U4','U5','U6','U7','U8','U9','UB','UC','UD','UE','UF','UG') ORDER BY c.acctcorp ASC, c.house ASC THE RESULTS I'M LOOKING TO RETURN... 9518 100256 6 PEARSON JAMES 59 !X 1/25/2008 9518 100256 6 PEARSON JAMES 59 UF 1/25/2008 9518 100256 6 PEARSON JAMES 59 CS 2/27/2008 The first two values match but the 3rd is different...I want to display all three records for this type of record set only.