RE: [PHP-DB] SQL

I tried the HAVING clause. it fails. This requires sub-selects or multiple tables -----Original Message----- From: DL Neil [mailto:PHPml@DandE.HomeChoice.co.uk] Sent: Wednesday, February 20, 2002 10:27 AM To: Jonathan Underfoot; Rick Emery; [PHP-DB] Subject: Re: [PHP-DB] SQL Jonathan, The original query doesn't work because the WHERE clause is evaluated earlier than the GROUP/COUNT(). Try putting num1 and num2 in a HAVING clause. Regards, =dn > 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..) > > > > How could you possibly have such a condition exist? That is, what > relation > > do the counts have to the contents of the name field? > > > > What ARE you trying to really accomplish? > > > > Is it possible to do a comparison of values from a count... > > > > for example... > > > > SELECT table1.name, count(table1.name) as num1, count(table2.name) as num2 > > FROM table1, table2 WHERE table1.name = table2.name AND num1 = num2 > > > > This sort of thing dosent work for me. How do I reffrence a count later > in > > the where clause?