I have an unfinished query, i am trying to test, basically im required
to get the value of the current field in a row and use it for a
subquery in that row :| Its not a working query, and im not asking for
someone to fix it, however as u can see i need to send the customerID
and month to the sub query. What its actually trying to do is tedious
to explain, but i have two tables of media usage for a customer, the
current month will be in the usage table, so that if the plan changes
in that month so does the totals, but for the previous months there is
a static month_totals table showing just the totals recorded. So im
needing to select two different tables depending on what month is being
selected. I hope this helps.
SELECT SQL_CACHE
CASE WHEN MONTH(mt.month) = MONTH(NOW())
THEN
CREATE VIEW current_month AS
SELECT
@customerID:=c.customerID, @month:=fu.month AS month,
DATE_FORMAT(fu.month,'%M') AS month_long,
@total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED]),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3))),
@percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,
@month_totals:=(SELECT month_total FROM month_totals WHERE
[EMAIL PROTECTED] AND DATE_FORMAT(month,"%m")[EMAIL PROTECTED] GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED]
AND [EMAIL PROTECTED]),
@feed_count:=(SELECT count(*) FROM feed_usage WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID
FROM producers_join WHERE producerID IN (3))),
ROUND(( IF(c.bandwidth_limit=0,
((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED],
((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut
FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID
INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3)) GROUP BY
fu.month,fu.customerID ORDER BY fu.month DESC
ELSE
CREATE VIEW previous_months AS
SELECT
@customerID:=c.customerID, @month:=MONTH(mt.month) AS month,
DATE_FORMAT(mt.month,'%M') AS month_long, @feeds:=feeds,
@total_bandwidth:=( SELECT bandwidth FROM month_totals WHERE
[EMAIL PROTECTED] AND MONTH(month)[EMAIL PROTECTED]),
@feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3))),
@percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,
@month_totals:=(SELECT month_total FROM month_totals WHERE
[EMAIL PROTECTED] AND DATE_FORMAT(month,"%m")[EMAIL PROTECTED] GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)),
@feed_count:=(SELECT count(*) FROM month_totals WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID
FROM producers_join WHERE producerID IN (3))),
ROUND(( IF(c.bandwidth_limit=0,
((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED],
((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut
FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID
INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3)) GROUP BY
fu.month,fu.customerID ORDER BY fu.month DESC
END
On 28/12/2005, at 4:47 PM, [EMAIL PROTECTED] wrote:
Dan Rossi <[EMAIL PROTECTED]> wrote on 12/27/2005 11:39:57 PM:
Hi there i am trying to use usewr variables in a select statement to
add to a where clause in a sub query. Ie
select @id:=id,@month:=month, (select SUM(totals) from table where
[EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table
its happened on other occasions ie with calculations and sums, whats
happened in mysql5 ? It used to work in mysql4 , something i am doing
is wrong ? Please let me know thanks.
Is there a great reason why you are using a subquery? I could rewrite
this
to avoid the subquery and probably eliminate your particular problem:
SELECT id, month, sum(totals) totals FROM TABLE group by id, month;
Unless(!) you oversimplified your original example. In which case, you
should post your actual query and I can give you a better response.
Technically, the values of the variables should not be determined until
AFTER the row is processed which means that you shouldn't be able to
use
them for your subquery (at least that's how I remember the SQL:2003
spec
but it's late and I could very well be wrong in my recollection)
Personally, I am not that big a fan of subqueries anyway. There are a
few
types of queries where they make the SQL to achieve a result rather
compact and elegant. However, I have never seen a subquery actually
outperform a properly constructed linear query. They sometimes match
linear performance but most often perform worse to much worse.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
