Here is my query.
INSERT INTO compare_moodle
JOIN mdl_role_assignments ra
ON mdl_user.id = ra.userid
JOIN mdl_role r
ON ra.roleid = r.id
AND r.id = 5
JOIN mdl_context c
ON ra.contextid = c.id
ON mdl_course.id = c.instanceid
It's possible to use a
VIEW in place of a table for the purpose you present. You'd do that like this:
CREATE OR REPLACE VIEW compare_moodle AS SELECT mdl_user.id AS userid, mdl_user.username, mdl_user.firstname, mdl_user.lastname, mdl_course.id AS courseid, mdl_course.fullname AS coursename FROM mdl_user JOIN mdl_role_assignments ra ON mdl_user.id = ra.userid JOIN mdl_role r ON ra.roleid = r.id AND r.id = 5 JOIN mdl_context c ON ra.contextid = c.id JOIN mdl_course ON mdl_course.id = c.instanceid;
This gives you a virtual table that is always transactionally up to date with your source tables. SQL best practice teaches us that this approach, without data redundancy, is the best way to do what you want.
The problem, of course, is performance. If you use this
compare_moodle view a lot you may have slowdowns. Correct indexing of your tables can practically eliminate those slowdowns. I suggest you get your application working with a view, and then figure out whether you have a performance problem.
What you asked for in your question is a so-called materialized view -- a view represented by an actual table. In some makes and models of DBMS you can give a command like
CREATE OR REPLACE MATERIALIZED VIEW ... and the DBMS does all the work. But, MySQL doesn't offer that.
There are various MySQLish approaches to managing materialized views. See here for more information. http://dba.stackexchange.com/questions/86790/best-way-to-create-a-materialized-view-in-mysql But this approach is a big hassle, and a maintenance headache compared to using an ordinary view.