I need to perform query, save the result and re-use it multiple times in one session (something like
WITH in Oracle DB).
This is the query I want to save:
SELECT `event`.id, event.name FROM attendant INNER JOIN event ON attendant.fk_event = event.id WHERE attendant.fk_user = 1 UNION SELECT `event`.id, event.name FROM organizer INNER JOIN event ON organizer.fk_event = event.id WHERE organizer.fk_user = 1 UNION SELECT `event`.id, event.name FROM invitation INNER JOIN event ON invitation.fk_event = event.id WHERE invitation.fk_user_to = 1
And then perform multiple queries like
SELECT id FROM savedquery WHERE ..... UNION SELECT id FROM savedquery WHERE .....;
TEMPORARY TABLEs but I don't think that I could use it since I need to perform this query for multiple different users at the time they run the request for events.
VIEW is also named, so I suppose there is the same problem.
What's the best solution to do this please?