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

"Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> 3/31/2009 10:49 AM >>>

Lance,

While there are often cases where an elegant SQL solution trumps a modular
solution, I like the modular approach since it's easier to follow its logic
(for mere humans anyway). This is where CTEs (Common Table Expressions) come
in handy.

In your example, you want unique addresses plus unique address/account
combos provided address does not repeat with a different account. As you've
found, the first question is real easy to answer and is displayed in the
Unique CTE in my example.
Second is a bit trickier, but I start it off as the first one (BaseDupes
CTE). Then I create an Exceptions CTE where address is the same, but the
account is different. Then I make sure I only pull in duplicates from the
BaseDupes CTE that are NOT in the Exception CTE (that's what the EXCEPTION
JOIN does).
Now I have two lists (unique addresses and unique account/address combos),
which I decided to join back to the original file so we can pull in other
fields (like the 'key' field).
In the end I order the final result set with an ORDER BY operator.

While there probably is a more elegant solution, this one is more inline
with the way humans break out problems.

with
Uniques as
(select address,count(*)
from myFile
group by address
having count(*) = 1),
BaseDupes as
(select account,address,count(*)
from myFile
group by account,address
having count(*) > 1),
Exceptions as
(select a.account, a.address
from BaseDupes a join BaseDupes b on a.address = b.address and
a.account <> b.account),
Dupes as
(select a.account, a.address
from BaseDupes a exception join Exceptions using(address,account))
select a.* from myFile a join uniques using(address)
union all
select a.* from myFile a join dupes using(account,address)
order by mykey

I hope that works for you and more importantly gives you another tool in
solving complex problems using SQL.

Elvis

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


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

SQL newbie trying to enter the 21st century.

I have this file that has, among other things, a key, an account, and an
address.
The account can occur more than once and when it does, its address is the
same.

I want all of the addresses that occur only once and their associated
accounts,
AND if the only reason an address is showing up multiple time is because
each
record has the same associated account number, I want that account address
pair as well.

e.g.

key account address
01 11 11 Main
02 12 12 Main
03 11 11 Main
04 13 12 Main
05 14 13 Main
06 15 14 Main
07 16 15 Main
08 17 16 Main
09 16 15 Main
10 18 17 Main


I want the account, address pair 14, 13 Main because 13 Main only occurs
once.
The same for 17, 16 Main and 18, 17 Main.

I want the account, address pair 11, 11 Main because - even though 11 Main
occurs
twice - each occurrence is with the same account.

I don't want 12 Main because it occurs twice but with different accounts.

I don't want 15 Main because it occurs three times, twice with the same
account but
once with a different one. If all three occurrences had been with the same
account,
I would have wanted it.

The records with unique addresses and their associated accounts I can get
by:

SELECT max(account), address FROM myfile GROUP BY
address HAVING count(address) = 1

But I am at a loss as how to add the records where the address count is
greater than 1 but only because every occurrence is associated with the
same account.

Is there some sort of recipe book for SQL? Syntax manuals are plentiful,
but is there one that tells you the SQL way to approach real-world problems?

I can sort the file by address and account extract the records in a single
pass
with RPG, but that is so 1992 ;-)

Thanks,
Lance