I'm trying to return all middle/highschool courses taught by a given teacher. With an inner join of two tables, 3 rows are displayed correctly. When I do 2nd inner join with the 3rd table, it returns 6 rows instead of 3.
Without using cte, DISTINCT, how can I display 3 rows with empid, the middle classes, and the high school classes? Also, both foreign tables should do joins with the primary table.
IF OBJECT_ID('tempdb..#empl') IS NOT NULL DROP TABLE #empl
IF OBJECT_ID('tempdb..#middlecourses') IS NOT NULL DROP TABLE #middlecourses
IF OBJECT_ID('tempdb..#highcourses') IS NOT NULL DROP TABLE #highcourses
create table #empl
(
EmpId int,
Grade int
)
insert into #empl select 1, 5
create table #middlecourses
(
EmpId int,
Grade int,
Course varchar(20)
)
insert into #middlecourses select 1, 5, 'Science'
insert into #middlecourses select 1, 5, 'Math'
insert into #middlecourses select 1, 5, 'English'
create table #highcourses
(
EmpId int,
Grade int,
Course varchar(20)
)
insert into #highcourses select 1, 5, 'Calculus'
insert into #highcourses select 1, 5, 'Physics'
insert into #highcourses select 1, 5, 'CompSci'
select e.empid, e.grade, m.course as 'MiddleCourse'
from #empl e inner join #middlecourses m
on e.empid = m.empid
and e.grade = m.grade
select e.empid, e.grade, m.course as 'MiddleCourse', h.course as 'HighCourse'
from #empl e inner join #middlecourses m
on e.empid = m.empid
and e.grade = m.grade
inner join #highcourses h
on e.empid = h.empid
and e.grade = h.grade
drop table #empl
drop table #middlecourses
drop table #highcourses
There might be a more elegant solution, but this should do the trick for the scenario given:
select e.empid, e.grade, c.course, c.CourseType
from #empl e
inner join
(
SELECT *, 'MiddleCourse' AS CourseType
FROM #middlecourses m
UNION ALL
SELECT *, 'HighCourse' AS CourseType
FROM #highcourses h
) c ON c.EmpId = e.EmpId AND c.Grade = e.Grade
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With