SQL Question
SQL Combine Join
Having trouble combining tables in a SQL join. I would like to combine both table A column of NationalAvg and table B column SchoolAvg in table C however none of my queries are correct.
Table A - Where I can get NationalAvg
Select Round(Cast(AVG(me.members_exams_score)as float), 2) as NationalAvg, e.exams_description
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
where me.members_exams_score is not null
group by e.exams_description
Results
+--------------------+-------------------+
|NationalAvg | exams_description |
+--------------------+-------------------+
| .78 | Medical Asst.Exam |
| .90 | Health Exam |
| .79 | EKG Exam |
| .81 | Phlebotomy |
+--------------------+-------------------+
Table B - Where I can get SchoolAvg
select Round(Cast(AVG(me.members_exams_score)as float), 2) as SchoolAvg, e.exams_description, s.schools_name
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
where me.members_exams_score is not null
group by e.exams_description, s.schools_name
order by s.schools_name
Results
+--------------------+-------------------+--------------+
|SchoolAvg | exams_description | School |
+--------------------+-------------------+---------------
| .90 | Medical Asst.Exam | School A |
| .88 | Health Exam | School A |
| .65 | EKG Exam | School A |
| .76 | Phlebotomy | School A |
| .93 | Medical Asst.Exam | School B |
| .79 | Health Exam | School B |
| .82 | EKG Exam | School B |
| .76 | Phlebotomy | School B |
+--------------------+-------------------+--------------+
Incorrect Combine Table - Wanting Both SchoolAvg & NationalAvg. The NationalAvg column is not showing up.
select Round(Cast(AVG(me.members_exams_score)as float), 2) as SchoolAvg, e.exams_description, s.schools_name
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
join
( select Round(Cast(AVG(me.members_exams_score)as float), 2) as NationalAvg
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
group by e.exams_description) nAvg on e.exams_id = nAvg.NationalAvg
where me.members_exams_score is not null
group by s.schools_name, e.exams_description
Results - are bring back the same table as B.
Intended Results -
+------------------+--------------------+-------------------+--------------+
| School Avg |NationalAvg | exams_description | School |
+------------------+--------------------+-------------------+--------------|
| .90 | .78 | Medical Asst.Exam | School A |
| .88 | .90 | Health Exam | School A |
| .65 | .79 | EKG Exam | School A |
| .76 | .81 | Phlebotomy | School A |
| .93 | .78 | Medical Asst.Exam | School B |
| .79 | .90 | Health Exam | School B |
| .82 | .79 | EKG Exam | School B |
| .76 | .81 | Phlebotomy | School B |
+------------------+--------------------+-------------------+--------------+
Thanks,
Answer
Try adding your logic to CTEs and joining that way.
With CTE1 as(
Select Round(Cast(AVG(me.members_exams_score)as float), 2) as NationalAvg, e.exams_description
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
where me.members_exams_score is not null
group by e.exams_description
),CTE2 as (
select Round(Cast(AVG(me.members_exams_score)as float), 2) as SchoolAvg, e.exams_description, s.schools_name
from members_exams as me
left join exams as e on e.exams_id = me.exams_id
join schools as s on s.schools_id = me.schools_id
where me.members_exams_score is not null
group by e.exams_description, s.schools_name
)
select A.exams_description, A.NationalAvg, B.schools_name, B.SchoolAvg
from CTE A
left Join CTE1 B ON A.exams_description = B.exams_description
Source (Stackoverflow)