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
fu.customerID=@customerID AND fu.month=@month),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
fu.customerID=@customerID AND fu.month=@month 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
customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customerID
AND month=@month),
@feed_count:=(SELECT count(*) FROM feed_usage WHERE
customerID=@customerID AND month=@month 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)*@feed_count,
((@month_totals*@percentage)*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
customerID=@customerID AND MONTH(month)=@month),
@feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE
customerID=@customerID AND fu.month=@month 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
customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)),
@feed_count:=(SELECT count(*) FROM month_totals WHERE
customerID=@customerID AND month=@month 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)*@feed_count,
((@month_totals*@percentage)*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, SGreen@stripped wrote:
> Dan Rossi <mysql@stripped> 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
>> id=@id and month=@month) 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
>
>
