I tried the HAVING clause.  it fails.  This requires sub-selects or multiple
tables


-----Original Message-----
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


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?


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to