Jump to content

Query on a query


johnbeamer

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.