SEO

I am working with SQL 8.00. I have the following tables


TABLE ClientInfo

CheckNum Account Name Addr1 City State Zip
---------------------------------------------------------------------
12345 11111 John 123 Mary St Miami FL 33139
54321 22222 Mary 321 River Side Clifton NJ 07055
98765 33333 Tom 12 Main St Miami FL 33139


and TABLE ClientAcct

CheckNum Account Cost Credit Notes
---------------------------------------------------------------------
12345 11111 1 Yes Great
12345 11111 11 Yes Well
12345 11111 111 No Bad
54321 22222 2 Yes Fine
54321 22222 22 No OK
98765 33333 3 Yes I like it


This the end result that I want.

CheckNum Account Name Addr1 City State Zip SUM of
Max(Notes)
of Cost or
Min(Notes)
-----------------------------------------------------------------------------
12345 11111 John 123 Mary St Miami FL 33139 123 Great
54321 22222 Mary 321 River St Clifton NJ 07055 24 Fine
98765 33333 Tom 12 Main St Miami FL 33139 3 I like it


I need to avoid duplicate rows(Note only if the Account field are
equal).
I need to get the fields shown above including the SUM of
ClientAcct.Cost and the MAX or Min of ClientAcct.Notes.

I have searched the web and
have tried DISTINCT, UNION, GROUP, COUNT(*) = 1 AND COUNT(*) <> 1 but
I can't get the correct results.

This statement give me too many rows(duplicate)
--------------------------------------
SELECT [Name], ClientInfo.account, addr1, City, State, ZIP, COST,
Notes from ClientInfo JOIN ClientAcct on
ClientInfo.CheckNum=ClientAcct.CheckNum

Does any body have a solution to this?

Thanks in advance
Julio

[posted and mailed, please reply in news]

Julio Allegue (jallegue@med.miami.edu) writes:
> This the end result that I want.
>
> CheckNum Account Name Addr1 City State Zip SUM of
> Max(Notes)
> of Cost or
> Min(Notes)
> --------------------------------------------------------------------------
>>---
> 12345 11111 John 123 Mary St Miami FL 33139 123 Great
> 54321 22222 Mary 321 River St Clifton NJ 07055 24 Fine
> 98765 33333 Tom 12 Main St Miami FL 33139 3 I like it

There is a general rule for this sort of questions what you should post
for this sort of question.

o CREATE TABLE statements for your tables.
o INSERT statements with sample data
o The output given the sample data.

If you do that, it is very easy by means to cut and paste to not only
write a solution, but also to *test* it.

One thing is not wholly clear from your post. Do you want the min/max
of the Note itself, or the Note that goes with the min/max cost? Your
text says the former, but your sample output indicates the latter, and
this is what I have assumed.

Thus, the suggestion that follows is untested, and you will have to refine
it yourself:

SELECT ci.CheckNum, ci.Account, ci.Name, ci.Addr1, ci.City,
ci.State, ci.Zip, s.Costsum, ca.Notes
FROM ClientInfo ci
JOIN (SELECT CheckNum, Account,
Costsum = SUM(Cost), mincost = MIN(Cost)
FROM ClientAcct
GROUP BY CheckNum, Account) as s ON ci.Account = s.Account
AND ci.CheckNum = s.CheckNum
JOIN ClientAcct ca ON ca.Account = ci.Account
AND ca.CheckNum = ci.CheckNum
AND ca.Cost = s.Cost

There is one thing that is disturbing with this solution: if there are
more than two or more rows that are tied for the min value, then you
will get all those rows. Since ClientAcct does not seem to have a unique
key, this is quite difficult to address.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp