Hi Jeff
OK, aliasing table is creating a copy of one table but calling it something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM revenue AS a,
revenue AS b, revenue AS c
I am referencing revenue three times but have aliased it as a, b, and c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of my head in rough
(very!) PHP , if you don't come right, I can sit down and do the code a
little more detailed
For simplification purposes, I am going to assume that you can alias tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest you check if this
is possible - if you can't there is a work around that just requires a bit
more thought....
//Assume you have an array that has all your client ids in
$client_id_array.
//Create base values based on the first id...
$select_base = "YEAR(1.date) AS year, MONTH(1.date) AS month,
> SUM(1.revenue) AS cust1_rev"
$for_base = "FROM revenue 1"
$predicate_base = "WHERE 1.customer_id = ".$client_id_array[0]
//Now loop through and append additional items to each string for each
instance of a client
//Start at 1 not zero as we already have accounted for the first id above
for($j=1; $j < count($client_id_array); $j++)
{
$select_base = $select_base.', SUM('.($j+1).') AS cust'.($j+1).'_rev';
$for_base = $for_base.', revenue '.($j+1);
$predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) =
YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND
'.($j+1).'.customer_id = '.$client_id_array[$j].')';
}
//Once your loop is done, put the parts together
$query = $select_base.$for_base.$predicate_base;
If you can't use numbers, you can use single letters, but that requires a
little more work incrementing ASCII numbers and then converting to letters -
also makes things way more complicated if you have more than 26 clients :)
but still doable.
HTH
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
----- Original Message -----
From: "Jeff McKeon" <[EMAIL PROTECTED]>
To: "Rory McKinley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, October 16, 2003 6:51 PM
Subject: RE: Challenging query....
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
