Topic: Query on a query

[johnbeamer]

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:

Code: [Select]

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

[fenway]

"another formula"? What does that mean?

[johnbeamer]

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

[johnbeamer]

Is it possible to use a "VIEW" for this ...?

[fenway]

It's not clearer... what do you mean by "call"?

[johnbeamer]

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:

Code: [Select]

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.

[fenway]

What makes you think you can't use it directly?

[johnbeamer]

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

[fenway]

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.