Generally I would accomplish this by breaking the SQL query into multiple smaller queries, but I’m trying to learn and do this the “right” way so here goes…
Concat(Customers.FirstName, ' ', Customers.LastName) as ListenerName,
Customers.EmailAddress as EmailAddress,
Customers.PhoneNumber as PhoneNumber,
Customers.BuyerPoints as BuyerPoints,
(SELECT COUNT(*) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.Status='New' OR Orders.Status='Pending' OR Orders.Status='Archived')) as CertsOrdered,
(SELECT SUM(CartContents.Price * CartContents.Quantity) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.Status='New' OR Orders.Status='Pending' OR Orders.Status='Archived')) as TotalPaid
FROM
Customers,
Orders
WHERE
Customers.BusinessUnit='$_SESSION[SESS_BIZUNIT]'
AND
Orders.Customer=Customers.ID
ORDER BY
Customers.LastName ASC
That’s the full query. The problem is that in each of my sub queries, let’s take the first COUNT one for example, I’m counting the number of items ordered from the CartContents table. However, it’s counting the total number of items ordered by all customers, not just the one customer and then looping. The syntax isn’t correct, but this is how I think it should be written:
(SELECT COUNT(*) FROM CartContents, Orders, Customers WHERE CartContents.Cart=Orders.Cart AND Orders.Customer=Customers.ID AND CartContents.Quantity>'0' AND (Orders.Status='New' OR Orders.Status='Pending' OR Orders.Status='Archived') AND Customers.ID=ParentQuery.Customers.ID) as CertsOrdered,
See the last AND statement in there? I need to equate the local Customers.ID with the Customers.ID from the parent query. How do I tell MySQL to go reference the ID field from the parent query?