List:General Discussion« Previous MessageNext Message »
From:J C Date:April 23 2003 9:25pm
Subject:Can I do this with UNION?(Or use temp HEAP table instead)
View as plain text  
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


Thread
Can I do this with UNION?(Or use temp HEAP table instead)J C23 Apr
  • RE: Can I do this with UNION?(Or use temp HEAP table instead)Adam Nelson24 Apr