Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL query returning double the rows with inner join of same table twice

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
like image 703
fdkgfosfskjdlsjdlkfsf Avatar asked Apr 17 '15 14:04

fdkgfosfskjdlsjdlkfsf


1 Answers

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
like image 71
jorgesalvador Avatar answered Nov 11 '22 16:11

jorgesalvador