mysql - Merging 2 SQL queries with a common field and AVGs -

my sql bit rusty after 2 years of not having studied it, can't find way this.

i need data moodle database, namely averages of feedback tests students rate teachers.

this sql query:

select mdl_course.id, mdl_user.username mdl_course inner join mdl_context on mdl_context.instanceid = mdl_course.id inner join mdl_role_assignments on mdl_context.id = mdl_role_assignments.contextid inner join mdl_role on mdl_role.id = mdl_role_assignments.roleid inner join mdl_user on mdl_user.id = mdl_role_assignments.userid mdl_role.id = 3 

returns this, table each course , teacher assigned.

course_id teacher    2          john 3          mary 4          john 

now, second sql this:

select mdl_feedback.course, avg(mdl_feedback_value.value) average mdl_feedback_value  inner join mdl_feedback_item on mdl_feedback_value.item = mdl_feedback_item.id  inner join mdl_feedback on mdl_feedback.id = mdl_feedback_item.feedback inner join mdl_feedback_completed on mdl_feedback.id = mdl_feedback_completed.feedback inner join mdl_user on mdl_feedback_completed.userid = mdl_user.id group mdl_feedback.course  course    average    2          3.5 3           3 4          3.25 

what want combine 2 sql queries 1 goes this, using course/course_id key

teacher    average john         3,375   <--- avg of 3,5 , 3,25 each of john's courses mary         3       <--- has 1 course no math here 

i'm not sure on how go this, appreciate bit of :) said, haven't used sql while i'm not keen in join thingies, maybe have use them have used them here.

i'm using mysql 5.5.33, , although related moodle, answer not moodle-centered thing important here tables output both queries.

thanks

if take each of subqueries , try join them, should work. added third field see detail of how score created.

select username, avg(average) average   , group_concat(concat('course: ', teachers.course_id,' score ', coalesce(average,'no score found'))) detail (     select mdl_course.id course_id, mdl_user.username username     mdl_course     inner join mdl_context on mdl_context.instanceid = mdl_course.id     inner join mdl_role_assignments on mdl_context.id = mdl_role_assignments.contextid     inner join mdl_role on mdl_role.id = mdl_role_assignments.roleid     inner join mdl_user on mdl_user.id = mdl_role_assignments.userid     mdl_role.id = 3 ) teachers left join (     select mdl_feedback.course course_id, avg(mdl_feedback_value.value) average     mdl_feedback_value      inner join mdl_feedback_item on mdl_feedback_value.item = mdl_feedback_item.id      inner join mdl_feedback on mdl_feedback.id = mdl_feedback_item.feedback     inner join mdl_feedback_completed on mdl_feedback.id = mdl_feedback_completed.feedback     inner join mdl_user on mdl_feedback_completed.userid = mdl_user.id     group mdl_feedback.course) scores   on teachers.course_id = scores.course_id group username