Consulta TSQL retornando o dobro das linhas com a junção interna da mesma tabela duas vezes

9

Estou tentando devolver todos os cursos do ensino médio / secundário ministrados por um determinado professor. Com uma junção interna de duas tabelas, três linhas são exibidas corretamente. Quando eu faço a 2ª junção interna com a 3ª tabela, ela retorna 6 linhas ao invés de 3.

Sem usar cte, DISTINCT, como posso exibir 3 linhas com empid, as classes intermediárias e as classes do ensino médio? Além disso, as duas tabelas estrangeiras devem fazer junções com a tabela principal.

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
    
por fdkgfosfskjdlsjdlkfsf 17.04.2015 в 16:28
fonte

5 respostas

1

Pode haver uma solução mais elegante, mas isso deve funcionar no cenário dado:

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
    
por jorgesalvador 17.04.2015 / 16:49
fonte
0

Isso ocorre porque todos os seus empid e grade são iguais. Esta junção está combinando muitas vezes.

Você já está vendo isso na sua primeira junção, as linhas em #Empl estão sendo duplicadas três vezes (porque corresponde a todos os 3 registros em #MiddleCourses ).

Para reduzi-los, você precisa usar uma associação mais exclusiva e / ou usar dados diferentes. Tente alterar o empid e o grade e você verá o que quero dizer.

    
por m.edmondson 17.04.2015 / 16:35
fonte
0

Sim, edmondson está correto. O que você poderia fazer é um simples pivot com um ROW_NUMBER () para tornar as linhas únicas.

select
    *
from
(
    select e.empid, e.grade, 'MiddleCourses' as [Type] , m.course, ROW_NUMBER() OVER (ORDER BY e.EmpId) ClassNo
    from #empl e inner join #middlecourses m
    on e.empid = m.empid 
    and e.grade = m.grade
    union all
    select e.empid, e.grade, 'HighCourses' as [Type] ,m.course, ROW_NUMBER() OVER (ORDER BY e.EmpId) ClassNo
    from #empl e inner join #highcourses m
    on e.empid = m.empid 
    and e.grade = m.grade
) SourceTable
pivot
(
    MIN(Course)
    FOR [Type] IN (MiddleCourses,HighCourses)
) pivotTable
    
por Joeri 17.04.2015 / 16:45
fonte
0

Você pode usar ROW_NUMBER() para corresponder middlecourses a highcourses dependendo da ordem alfabética course :

select e.empid, e.grade, m.course as 'MiddleCourse', h.course as 'HighCourse'
from #empl e 
cross apply (
   SELECT course, ROW_NUMBER() over (order by course) as rn
   FROM #middlecourses m
   WHERE e.empid = m.empid AND e.grade = m.grade ) m
cross apply (
   SELECT course, ROW_NUMBER() over (order by course) as rn
   FROM #highcourses h
   WHERE e.empid = h.empid AND e.grade = h.grade ) h
where m.rn = h.rn

Saída:

empid   grade   MiddleCourse    HighCourse
-------------------------------------------
1       5       English         Calculus
1       5       Math            CompSci
1       5       Science         Physics

O acima só funcionará caso haja um número igual de middlecourses e highcourses .

Caso haja uma incompatibilidade entre o número de middlecourses e highcourses , você pode usar uma variação um pouco mais complicada da consulta acima:

SELECT e.EmpId, e.Grade, t.MiddleCourse, t.HighCourse
FROM #empl e
INNER JOIN (
   SELECT COALESCE(m.empid, h.empid) AS empid, 
          COALESCE(m.grade, h.grade) AS grade,
          m.Course AS 'MiddleCourse', h.Course as 'HighCourse'
   FROM (SELECT empid, grade, course, 
                ROW_NUMBER() over (partition by empid, grade 
                                   order by course) as rn
         FROM #middlecourses) m
   FULL JOIN (SELECT empid, grade, course, 
                     ROW_NUMBER() over (partition by empid, grade 
                                        order by course) as rn
              FROM #highcourses) h 
   ON m.EmpId = h.EmpId AND m.Grade = h.Grade AND m.rn = h.rn ) t
ON e.EmpId = t.empid AND e.Grade = t.grade

Com mais um registro em highcourses :

insert into #highcourses select 1, 5, 'Algebra'
A saída

é:

EmpId   Grade   MiddleCourse    HighCourse
-------------------------------------------
1       5       English         Algebra
1       5       Math            Calculus
1       5       Science         CompSci
1       5       NULL            Physics
    
por Giorgos Betsos 17.04.2015 / 16:42
fonte
0
  

Antes de mais nada, você precisa entender como a junção interna funciona. Junção interna   vai te dar o registro que estão presentes em ambas as tabelas que você   estão se juntando.

chegando à sua pergunta, quando você executa a seguinte consulta

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

você receberá este registro.

empid   grade   MiddleCourse
1   5   Science
1   5   Math
1   5   English

para que você obtenha 3 registros que são esperados, porque há 3 registros na tabela #liddlecourse com empid = 1. Portanto, junção interna é um trabalho assim. Escolherá um empid de cada vez da #empl table e tentará encontrar as linhas correspondentes para este empiid na segunda tabela, por exemplo, #middlecourses

Então você tem 3 registros da consulta acima. Agora, quando você adicionar a segunda junção interna, ela tentará obter o empid acima do registro acima e corresponderá à terceira tabela que é #highcoures. Então, para cada empírico, ele retornará 3 registros. então, no total, você terá um registro 3 * 3 = 9 da segunda consulta como essa.

EmpId   Grade   EmpId   Grade   Course  EmpId   Grade   Course
1        5      1       5       Science  1      5      Calculus
1        5      1       5       Math     1      5      Calculus
1        5      1       5       English  1      5      Calculus
1        5      1       5       Science  1      5      Physics
1        5      1       5       Math     1      5      Physics
1        5      1       5       English  1      5      Physics
1        5      1       5       Science  1      5      CompSci
1        5      1       5       Math     1      5      CompSci
1        5      1       5       English  1      5      CompSci

Uma solução para este cenário seria unir em sua tabela de cursos e então fazer a junção interna com a tabela #empl.

select e.EmpId, e.Grade, a.Course from #empl e
inner join (
select * 
from #middlecourses
union
select * from #highcourses) a on e.EmpId = a.EmpId
    
por Mukund 17.04.2015 / 16:54
fonte