Jonathan,
You didn't mention which DBMS you're using (Oracle, MySQL, MS SQL, ...) and
the are each a little different but I'll try to solve with fairly generic
SQL:
My personal preference is keep it simple, break up the SQL if it helps (and
I think it would help here).
I'd use a third table, it will make this very simple.
/* not sure what datatype cardnumber is but it doesn't matter */
/* just make this match */
CREAT TABLE TroubleItems
(cardnumber int,
numAuth int,
numCharg int)
INSERT INTO TroubleItems
SELECT cardnumber, count(*), 0
FROM authtable
GROUP BY cardnumber
UPDATE TroubleItems
set numCharg = (SELECT count(*)
FROM chargetable
WHERE TroubleItems. cardnumber = chargetable. cardnumber)
/* delete the ones that do match (they aren't trouble) */
DELETE TroubleItems
WHERE numAuth = numCharg
/* Now the report you want is easy */
SELECT * FROM TroubleItems
/* then drop or empty TroubleItems - or delete the rows as they are fixed */
/* but don't run this again unless you empty TroubleItems */
I did test this on MS SQL server and it works fine there. The only thing
I'm the least fuzzy on is will this update statement work in MySQL (MySQL
is a little weak with sub-queries). Even if that command fails you can walk
the list by selecting the cardnumber and doing a for each loop inside of PHP
- this would take a little longer but it would give you valid results.
Good Luck,
Frank
On 2/19/02 6:16 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> From: "Jonathan Underfoot" <[EMAIL PROTECTED]>
> Date: Tue, 19 Feb 2002 16:28:32 -0500
> To: "Rick Emery" <[EMAIL PROTECTED]>, "[PHP-DB]" <[EMAIL PROTECTED]>
> Subject: Re: [PHP-DB] SQL
>
> ok... authorizations and charges... two tables..
>
> Card Number 1 gets authorized 20 times for use (20 rows in the
> authorizations table) and when the transaction is completed they get charged
> 20 times (20 rows in the charges table) .. however we've been finding that
> authorizations are not always ending in charges. (Essentially people are
> cheating the system) and I need to find out who. Card numbers are the
> same... I ran this without the num1=num2 and it gave me very bad results.. I
> think my counts are off base. num1 and num2 were the same for all of them.
> I know this isn't the case.
>
> so...
>
> SELECT authtable.cardnumber, count(authtable.cardnumber) as num1,
> count(chargetable.cardnumber) as num2 FROM authtable, chargetable WHERE
> authtable.cardnumber = chargetable.cardnumber AND num1 != num2
>
> is what I'm shooting for logically.. once again.. I think the counts are
> messed up... (I might be completely off base here..)
>
> Thanx,
>
> -Jonathan
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
