I tried the HAVING clause. it fails. This requires sub-selects or multiple
From: DL Neil [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 20, 2002 10:27 AM
To: Jonathan Underfoot; Rick Emery; [PHP-DB]
Subject: Re: [PHP-DB] SQL
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.
> 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
> 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..
> think my counts are off base. num1 and num2 were the same for all of
> I know this isn't the case.
> 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
> > 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
> > 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
> > the where clause?
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php