Left Join with same table and group by is returning duplicated tuples with reverse order2020-05-26 sql join group-by hsqldb
I'm trying to query a database(not owned by me) that contains the following columns :
NumEpoca (epoch), Turma(class), Dia (day - indicates day of the week), Hora (hour - each value indicates a 30mins time, a 3h class generate 6 tuples), Disciplina (course), TipoAula (type of class, theoretical or practical), Sala (classroom)
This is basically a class schedule, so for a given Class, in the same day of the week I can have in one week a practical class and in the other a theoretical class.
Now, I want to get for a given day, the minimum and max hour (so I can calculate the starting and ending hour of the class), but I also want to get the classrooms for them, and I don't know at priori, if it's going to be a theoretical or practical class.
Also, certain classes are only practical, and some are only theoretical, so I just want 1 classroom.
The query I'm doing, gives me basically everything but
LectureDetails(beginDate=2020-05-26 15:30:00, endDate=2020-05-26 18:30:00, classroom=L_H1/G.0.08) LectureDetails(beginDate=2020-05-26 15:30:00, endDate=2020-05-26 18:30:00, classroom=G.0.08/L_H1)
as you can see here, I get the same class (that starts and ends at the same hour, for the 2 classrooms Teo and Pract. But I only need 1 tuple for that day and I'm getting L_H1/G.0.08 and G.0.08/L_H1.
"SELECT a1.Dia,MIN(a1.Hora),MAX(a1.Hora),a1.Sala, a2.Sala FROM Aulas as a1 LEFT JOIN Aulas as a2 " + "on a1.Sala <> a2.Sala and a1.Disciplina = a2.Disciplina and a1.NumEpoca = a2.NumEpoca and a1.Turma = a2.Turma " + "and a1.Dia= a2.Dia and a1.Hora = a2.Hora " + "where NumEpoca = ? AND Turma = ? AND Disciplina=? GROUP BY a1.Dia,a1.Sala,a2.Sala"
Thanks in advance.
You could have eliminated the mis-ordered results by using
a1.Sala < a2.Sala instead of the inequality.
But that's not really the approach you want anyway. Try something like this:
SELECT Dia, MIN(Hora), MAX(Hora), MIN(Sala), CASE WHEN MIN(Sala) = MAX(Sala) THEN NULL ELSE MAX(Sala) END FROM Aulas WHERE NumEpoca = ? AND Turma = ? AND Disciplina = ? GROUP BY Dia
- Left Join without duplicate rows from left table
- Which table exactly is the "left" table and "right" table in a JOIN statement (SQL)?
- MySQL LEFT JOIN 3 tables
- SQL left join vs multiple tables on FROM line?
- JOIN, ORDER BY and GROUP BY in same statement
- SQL join on multiple columns in same tables
- What's the best way to join on the same table twice?
- How correct query for group by when joining table using left join
- Problem joining tables where joined table needs to be ordered before grouping