RE: Challenging query....

[Jeff McKeon]

> If you have a way to generate the query code dynamically 
> (e.g. using a loop in C, PHP etc.), you can build a query 
> using aliased tables :
> 
> SELECT YEAR(a.date) AS year, MONTH(a.date) AS month, 
> SUM(a.revenue) AS cust1_rev,
> SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev
> FROM revenue a, revenue b, revenue c
> WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND 
> MONTH(b.date) =
> MONTH(a.date) AND b.customer_id = 2)
> AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = 
> MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month
> 

Ok, that looks promising as I'll be using PHP, but I'm a little fuzzy on
the logic you've set.  What are "aliased tables" and how would I define,
use them in an loop?

Thanks,


Jeff