Is it possible to query a query?

[p0c79]

I have a query which brings back a set of test scores for a student from a table that holds the scores for a whole class. Can I treat the $Result set that the query produces as a table and write things like:

$Result2="Select sum(testscores) AS ScoreTotal FROM $Result";

this could be rubbish I don't know?

I would like to do this as I want to first of all print all the data brought back from the first query and then process that data again to produce a total score for the student. I could just write a whole new query but would my suggestion be more efficient?

[CrazyCrane]

You can't do it exactly like that, because you're trying to perform a SQL query on a set of results, not on a database. You could certainly write some PHP code to calculate all of the values you need - you've already selected the data from the database, so it's just a case of manipulating it.

If you're planning on writing the information out, that implies you're looping through the results to do this. You could simply use a variable to do the sum that you need, adding to it as you loop through the results. After you've written all the results out, this variable would contain the sum of all the records, so there'd be no need to do another query or anything.

In general, calls to the database are inefficient, so it's usually best to avoid it when there's a simple alternative.

Hope that made some sense .

[p0c79]

That makes a lot of sense thankyou. I think I know what to do now.

[freakysid]

MySQL does its job well - but it is not a proper relational database management system and this is one area where it fails. If you were using a proper RDMS that supported ANSI SQL92 then you could perform nested queries in the one sql statement, as you want to do (query the results of a query). That's the bad news.

The good news is that there is most often a way around these limitations. Even though MySQL doesn't implement a very full version of the SQL language - it is still pretty good at most things. So if you tell us what your table/s schemas are and the results you want, someone will no doubt be able to show you how to write a query for what you want.