YUP. My patented <grin> use of MAX to come up with a single value in table 1
SELECT t1.patnumbr,t1.rdate,t1.rtime,max(t1.totalcharge),sum(t2.charge)
FROM table1 t1, table2 t2
WHERE t1.patnumbr = t2.patnumbr
AND t1.rdate = t2.rdate
AND t1.rtime = t2.rtime
GROUP BY t1.patnumber,t1.rdate,t1.rtime
HAVING max(t1.totalcharge) <> sum(t2.charge)
(You can use MIN, AVG or any other function that returns a single
value for totalcharge while excluding it from the grouping list.)
MJS <[EMAIL PROTECTED]> wrote:
>I have 2 tables. The first has 4 columns, patnumbr, rdate rtime, and totalcharge
>(integer, date, time, and currency). It is linked to a second table in a 1 to many
>relationship, where the columns are nearly the same....patnumbr, rdate, rtime, and
>charge. Each row in the first table is linked to a group of rows in the second
>table. The matching columns are the 1st 3 columns. As you might guess, the sum of
>the charges in the second table is the value of totalcharge in the first table. Now
>say something happens to corrupt the data, where the sum of the charges in the second
>table does not match the value in totalcharge, and I need to find out which row in the
>first table does not have the right value for totalcharge. Is there some really cool
>select statement, with group by and having or other forms of SQL magic that will find
>all off the rows in the first table that don't have the correct value in the
>totalcharge column???? It's easy to do with a declare cursor, but I was hoping that a
>select statement would work!
>
>TIA!
>
>Mike Sinclair
>
>
>
__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas. Experience the
convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
