Jay Jay - 2 months ago 5
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
Comments