I have a MySQL query which uses 3 tables with 2 inner joins. Then, I have to find the maximum of a group from this query output. Combining them both is beyond me. Can I break down the problem by storing the output of the first complicated query into some sort of temporary table, give it a name and then use this table in a new query? This will make the code more manageable. Thank you for your help.
This is very straightforward:
CREATE TEMPORARY TABLE tempname AS (
SELECT whatever, whatever
FROM rawtable
JOIN othertable ON this = that
)
The temporary table will vanish when your connection closes. A temp table contains the data that was captured at the time it was created.
You can also create a view, like so.
CREATE VIEW viewname AS (
SELECT whatever, whatever
FROM rawtable
JOIN othertable ON this = that
)
Views are permanent objects (they don't vanish when your connection closes) but they retrieve data from the underlying tables at the time you invoke them.