Dan,
You need to shoot your SQL tutor. Whoever taught you to write aggregate
queries seriously took your money. You DO NOT need to use subqueries to do
what you want to do. You do not need to write a full CREATE TABLE
statement to create a temporary table (see other response). You do not
need a FUNCTION or a STORED PROCEDURE or a VIEW.
Please, get back to the basics. Re-read the appropriate parts of the
manual and stop trying to make this harder than it should be. At most,
this will take anywhere from 2 to 5 statements. You seem to have become so
impressed with subqueries that you are trying to make a square peg fit
into a round hole. IMHO using subqueries is not the optimal, effective,
preferred, recommended, or suggested way to write this query.
I am more than willing to help you to refactor your query and I am sure
there will be others on this list if you don't want to deal with me any
more. But please take my advice and take the simpler, more direct
approach.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Dan Rossi <mysql@stripped> wrote on 12/28/2005 11:55:35 PM:
> I just tried to create a Function or Stored Procedure instead of making
> variables but it didnt even let me do this
>
<snip>
>
> On 29/12/2005, at 3:31 PM, Dan Rossi wrote:
>
> > 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.
<snip>
> >> 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
<snip>
