Re: SQL

Actually, I'm running MS SQL which should allow me to sub-select.... Usually I work in MySQL -J ----- Original Message ----- From: "Frank Flynn" <frank@declan.com> To: <php-db@lists.php.net> Cc: <jonathan@underfoot.cc> Sent: Tuesday, February 19, 2002 11:59 PM Subject: Re: [PHP-DB] SQL > 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, "php-db-digest-help@lists.php.net" > <php-db-digest-help@lists.php.net> wrote: > > > From: "Jonathan Underfoot" <jonathan@underfoot.cc> > > Date: Tue, 19 Feb 2002 16:28:32 -0500 > > To: "Rick Emery" <remery@excel.com>, "[PHP-DB]" > > <php-db@lists.php.net> > > 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 > >