RE: SQL question

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.