Sub Query - Need Help with Scope

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?

This seems to be referencing the same problem, probably worded better that i have above. Still not understanding the solution.

http://stackoverflow.com/questions/1395329/can-layered-subqueries-spanning-multiple-tables-reference-a-parent-query

Ok, I think I’ve got it. I just added an alias to the column in the parent query and was then able to reference that alias in the sub query. Seems too simple.

Is there a way to set an alias in the parent query without actually selecting the column?

Put an alias on the table, and then reference that in the subquery