FN-FORUM: MySQL Query Problem

date posted 13th March 2006 11:25

Hello,

I have an application and am joining 4 tables in a query and I need to
return a COUNT of one column from the 4th table instead of the actual data.
Theoretically, a COUNT should return zero when there is no data, but for
some reason it returns no data in my query. When there are matching entries
in the 4th table it happily returns the data. I'm sure this is just a case
of me not seeing the obvious in the SQL, so if somebody could offer a fresh
pair of eyes and some pointers I'd appreciate it.

Here's the MySQL statement:

$sql = 'SELECT u.*, a.*, p.*, COUNT(o.oCustomerID) AS numOrders
FROM cUsers AS u, oOrders AS o
LEFT JOIN cAuthority AS a ON u.uSecurityLevel =
a.aID
LEFT JOIN uProfiles AS p ON u.uID = p.pUserID
WHERE a.aLevel "Customer" AND u.uID =
o.oCustomerID
GROUP BY o.oCustomerID
ORDER BY p.pLastName' ;

It's the COUNT(o.oCustomerID) that's causing me grief. I take it out and I
get rows returned. I add some data into the oOrders table and I get rows
returned. I take out the data in oOrders and I get nothing.

And on a Monday too!

Thanks
Edward