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.
As an Amazon Associate we earn from qualifying purchases.