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

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,

On 2/19/02 6:16 PM, "[EMAIL PROTECTED]"

> From: "Jonathan Underfoot" <[EMAIL PROTECTED]>
> Date: Tue, 19 Feb 2002 16:28:32 -0500
> 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 (
To unsubscribe, visit:

Reply via email to