Hi All,
Wondering if its worth it for me to upgrade to MySQL 4.0 in order to use
UNION. I need to do multiple select statements to the same three tables
like:
SELECT p.company, p.plan_name, r.cost, r.extra_fee, SUM(r.cost) as
total_cost
FROM product p, location l, rates r
WHERE l.state = ? AND
l.product_key = r.product_key AND
r.minimum_age <= ? AND
r.maximum_age >= ? AND
r.minimum_amount <= ? AND
r.maximum_amount >= ? AND
r.product_key = p.product_key
UNION ALL
[ Same SELECT statement as before for how many people are being quoted (can
be 10 times) ...]
GROUP BY p.plan_name
(placeholders would be state, age, age, amount, amount)
I am looking to take all the available plans and show them with the sum of
each individual cost - is this how I would do this?
FYI- Currently I have MySQL 3.23 and was planning on doing this in the
following manner(but am having trouble):
CREATE TEMPORARY TABLE IF NOT EXISTS query TYPE=HEAP SELECT p.company,
p.plan_name, p.plan_type, r.premium, r.policy_fee
FROM location l, rates r, product p
WHERE
l.state = ? AND
l.product_key = r.product_key AND
r.minimum_age <= ? AND
r.maximum_age >= ? AND
r.minimum_amount <= ? AND
r.maximum_amount >= ? AND
r.product_key = p.product_key
Loop thru this x(1-10) amount of times and then do a SELECT against this
temp table to group and sum cost.
So can I do what I want to do with UNION or am I better off staying with the
temp table?
Justin
