RE: SQL question

["Lance Gillespie" <lgillespie@xxxxxxxx>]

Thank you Santiago, both of those expressions worked fine and
got the same results.

So many ways to get the same result!

SQL is proving to be - interesting.

Thanks,
Lance



> > > Santiago G Martí <smarti@xxxxxxxx> 4/1/2009 12:15 AM >>>
I think I arrive a bit late but, just for clarifying, try this:
SELECT      a.*
  FROM                     myFile a LEFT EXCEPTION JOIN
    (SELECT b.*       FROM myFile b
                INNER JOIN myFile c USING(address)
      WHERE c.account<>b.account) d USING(address)

or the same, in another way:
WITH notWanted AS
    (SELECT b.*
       FROM myFile b INNER JOIN
            myFile c USING(address)
      WHERE c.account<>b.account)
SELECT a.*
  FROM myFile    a LEFT EXCEPTION JOIN
       notWanted d USING(address)






                                                                       
   
             "Lance Gillespie"                                         
   
             <lgillespie@cvwd.                                         
   
             org>                                                    
Para 
             Enviado por:              "'Midrange Systems Technical    
   
             midrange-l-bounce         Discussion'"                    
   
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
   
                                                                       
cc 
                                                                       
   
             31/03/2009 23:32                                      
Asunto 
                                       RE: SQL question                
   
                                                                       
   
                Por favor,                                             
   
                responda a                                             
   
             Midrange Systems                                          
   
                 Technical                                             
   
                Discussion                                             
   
             <midrange-l@midra                                         
   
                 nge.com>                                              
   
                                                                       
   
                                                                       
   




YES!
Both work.

Now to understand why!

Thanks again,
Lance


> > > "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> 3/31/2009
1:25
PM >>>
Thinking about it a bit more, this might work as well:

select a.*
from myFile a exception join
     myFile b join myFile c on b.address = c.address and b.account <>
c.account
     on a.account = b.account and a.address = b.address

Join the mismatched values (b to c) then get the exceptions (a to b).

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com 


-----Original Message-----
Subject: RE: SQL question

Thank you, Elvis.  When I ran your suggestion, I got:

KEY   ACCOUNT  ADDRESS
  1      11    11 MAIN
  3      11    11 MAIN
  5      14    13 MAIN
  7      16    15 MAIN
  8      17    16 MAIN
  9      16    15 MAIN
 10      18    17 MAIN

which is very nearly what I want.
Unfortunately, it grabs key 7 and 9, which
it should not because key 6 (which is wrong in
my e-mail and should be account 15, address 15 Main)
gives a different account number for the same address.

Your code gives me a whole to tool, CTE's, so I am going
to look into that approach. It seems, as you say, a good
way to break down the problem.

Thanks again,
Lance



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.