Btwi dont want the column of a view to be a variable, i think thats
what it thinks ! Im just needing to send the value of the current
primary key field top a sub query !
Read my latest post if i can get around not using variables, and still
manage to get the right values of a current row going to a sub query ?
On 29/12/2005, at 2:48 AM, SGreen@stripped wrote:
>
> You seem to be coming at SQL with a COBOL perspective. Views are
> something you typically create just once and they stay updated
> automatically. They work like tables not like queries. Assigning
> variables to each column of a view doesn't make any sense (in the SQL
> sense of "view") as each column could potentially contain several
> million values, depending on how many rows you identify for your VIEW
> in your definition statement.
>
> You demonstrated your situation with a conditional branch on a value
> to produce one of two different . What I think you wanted to do was to
> UNION two queries together so that you could combine the history of a
> customer's account with the current month's activity into a single set
> of results.
>
> Your queries are also insensitive to year changes. What happens when
> you are in Jan 2006 and you need to review the previous month's data?
>
> Since you are using views I know you are on v5.x or higher. However, I
> think you have become so entangled with your subqueries that you have
> managed to confuse yourself. Using a subquery to calculate every value
> of each row is VERY inefficient and unless you are actually trying to
> prove just how bad it is, I strongly suggest you modify your approach
> to become more linear, perhaps completely linear.
>
> Here is an example of how to combine two queries into a single result:
>
> (
> SELECT fu.customer_id
> , fu.month
> , sum(fu.usage) total_bandwidth
> , sum(if(p.producerID is NULL, 0, fu.usage)) feed_bandwidth
> , sum(fu.usage)/sum(if(p.producerID is NULL, 0, fu.usage)) percentage
> , count(distinct f.feedID) count
> , count(distinct if(p.producerID is null, null, f.feedid) feed_count
> FROM feed_usage fu
> INNER JOIN customers c
> ON fu.customerID=c.customerID
> INNER JOIN feeds f
> ON fu.feedID=f.feedID
> LEFT JOIN producers_join p
> ON f.feedID = p.feedID
> AND p.producerID IN (3)
> GROUP BY fu.month,fu.customerID
> )
> UNION
> (
> SELECT customerid
> , month
> , total_bandwidth
> , feed_bandwidth
> , percentage
> , count
> , feed_count
> FROM month_totals
> )
> ORDER BY fu.month DESC;
>
> See how I disentangled so many of your subqueries? If you are having
> speed problems, we can work on that after we get the query working,
> OK? ALSO(!) you don't refer to a value in the outer query by its
> variable name (because you normally don't use variables in
> subqueries), you normally use the actual column name in the subquery.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> Dan Rossi <mysql@stripped> wrote on 12/28/2005 01:10:32 AM:
>
> > 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
> > >
> > >
> >
