NOTE - I HAVE PROVIDED MORE DETAILS AND AN EXAMPLE AND USED CODE BLOCKS (THANKS TO THE HELPFUL COMMENTS ON THIS POST) IN THE QUESTION
DEFINING VIEWS IN MYSQL FROM COMPLEX QUERIES -- TRY #2
I'M NOT SURE HOW TO DEPRECATE OR TURN OFF THIS QUESTION... PLEASE SEE MY OTHER POST.
THANKS,
DOXGUY
So the following code works (gives anticipated results):
SELECT some-fields FROM (SELECT --code block 1-- ) t1 LEFT JOIN (SELECT --code block 2-- ) t2 ON --code block 3-- GROUP BY --more fields--
But if I try to turn this into a view, by putting "CREATE OR REPLACE VIEW myview AS" as the top line, I get a warning that I can't have these subqueries as part of the FROM statement when I create a view. So I thought I'd try creating little intermediate views like this:
CREATE miniview1 AS SELECT --code block 1--
CREATE miniview2 AS SELECT --code block 2--
CREATE myview AS SELECT some-fields FROM miniview1 t1 LEFT JOIN miniview2 t2 ON --code block 3-- GROUP BY --more fields--
See... everything exactly the same except that I create the miniviews as an intermediate step. I even know that miniview1 and miniview2 look exactly as I expect. But then when I create the final "myview", the merging together is all out of order and wrong.
Any ideas would be greatly appreciated. I hope it's ok on this forum to post with the --code block 1-- bit, I thought it was a little to complicated to explain the actual variables.
Thanks!