# Left Join with same table and group by is returning duplicated tuples with reverse order

2020-05-26 sql join group-by hsqldbI'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.

Answers

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
```

Related

- 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