johnbeamer Posted December 11, 2007 Share Posted December 11, 2007 Hello I am running MySQL 5.0.21 and am trying to run a query on a query and not sure how to do it. I was hoping someone could advise. I have a table that has a whole ton of fields. I call the following query on this table: SELECT *, -(vy0+sqrt(vy0*vy0-2*(y0-17/12)*ay))/ay AS time_to_plate, if(y0=50,-(vy0+sqrt(vy0*vy0-2*(50-17/12)*ay))/ay,if(y0=55,(-sqrt(vy0*vy0-2*(55-17/12)*ay)+sqrt(vy0*vy0-2*5*ay))/ay,if(y0=40,(-sqrt(vy0*vy0-2*(40-17/12)*ay)+sqrt(vy0*vy0+20*ay))/ay,0))) as time_50ft FROM pitches LEFT JOIN (atbats LEFT JOIN players ON atbats.pitcher = players.eliasid) ON pitches.ab_id = atbats.ab_id WHERE players.last = 'Smoltz' AND players.`first` = 'John' AND pitches.start_speed IS NOT NULL You can see the complex formula that I use. I now need to use this calc (eg, time_to_plate) as part of another formula. Hence I wanted to query this query but can't seem to do it in MySQL. You can do it in Access so I assume there is a work around. Help VERY much appreciated Thanks John Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2007 Share Posted December 11, 2007 "another formula"? What does that mean? Quote Link to comment Share on other sites More sharing options...
johnbeamer Posted December 11, 2007 Author Share Posted December 11, 2007 Sorry, I should have been clearer. For example in a new query (ideally calling the query above) the statement would read select (time_to_plate*PI()*sqrt(time_50ft)/2) AS final_time, (time_to_plate*time_50ft) AS total_time My understanding (and I am not a sql expert) is that I can't call the time_to_plate and time_50ft as it is part of a query not a table .... Hope that is a bit clearer ... Thanks Quote Link to comment Share on other sites More sharing options...
johnbeamer Posted December 12, 2007 Author Share Posted December 12, 2007 Is it possible to use a "VIEW" for this ...? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 12, 2007 Share Posted December 12, 2007 It's not clearer... what do you mean by "call"? Quote Link to comment Share on other sites More sharing options...
johnbeamer Posted December 13, 2007 Author Share Posted December 13, 2007 I mean that I want to use a field generated in one query in another query. Here is a simple example. Suppose I have a table called "exams" with three fields "maths score", "english score", "science score" I write my first query: select (maths score + english score) AS mathsenglish FROM exams Then I want my next query to use mathsenglish that I generated above. For instance I might want to multiple "mathsenglish" by "science score". Obviously in this simple example you could just generate the "mathsenglish" output again in the select statement but if you had a really complex formula (like I do above) it would be more elegant to somehow call (apologies for misuse of the word -- I don't know the technical terms) the mathsenglish field I generated in my query. I think I can use a nested select but surely there is an easier way. In MS Access you can write a query on a query so I assume there is someway in MySQL. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 13, 2007 Share Posted December 13, 2007 What makes you think you can't use it directly? Quote Link to comment Share on other sites More sharing options...
johnbeamer Posted December 13, 2007 Author Share Posted December 13, 2007 Well it gives me an error (1054 - Unknown Column 'xxxx' in 'field list'), which means either (a) you can't do it (b) I don't know what I am doing Quote Link to comment Share on other sites More sharing options...
fenway Posted December 13, 2007 Share Posted December 13, 2007 Well, there are certainly ways you can't using it... since the name of the expression is only generated *after* the query is run, you can using it order by / having statements. If you want to use it, you can either (a) use the underlying expression (not the named version), or (b) join it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.