Re: FN-FORUM: MySQL Query Problem

date posted 13th March 2006 17:21


> 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.
>

Returning nothing in that case sounds right, in that you're trying to
join the other tables to a o.oCustomerId which isn't there in the o
table (the 'AND u.uID = o.oCustomerID' sub-clause), and so you get
nothing back. If the aim of this query is to produce a count for all the
customers with at least one entry in the o table and a zero otherwise,
possibly what you want is a union of what you have and something like:

SELECT u.*, a.*, p.*, 0 AS numOrders where [...] and u.uid not in
(select oCustomerID from o ) [...]

Assuming your mysql version supports this.

Graham

> And on a Monday too!
>
> Thanks
> Edward
--
Graham Stark, Virtual Worlds
phone: (+044) 01908 618239 mobile: 07952 633185
skype: graham_k_stark
Homepage http://www.virtual-worlds.biz/people/graham_s
Virtual Chancellor http://www.virtual-worlds.biz/vwc