Strange behavior in DB - Too many records

You should merge tables using JOINS and not WHERE

The top 5 JOIN types are:

INNER JOIN

Returns a row for each occurrence of a given relationship between two tables

example: select tabela1.* , tabela2.* from tabela1 inner join tabela2 ON tabela1.id = tabela2.fk_tabela1

LEFT JOIN

Regardless of the relation being served, returns all rows in table1. If the relation is satisfied, it also returns the data of table2, otherwise the data of table1 is filled and that of table2 is NULL

example: select tabela1.* , tabela2.* from tabela1 left join tabela2 ON tabela1.id = tabela2.fk_tabela1

RIGHT JOIN

Regardless of the relation being served, returns all rows in table2. If the relation is answered also returns the data of table1, otherwise the data of table2 is filled and that of table1 is NULL

example: select tabela1.* , tabela2.* from tabela1 right join tabela2 ON tabela1.id = tabela2.fk_tabela1

FULL JOIN

Returns the relationships served and both the occurrences of table 1 not related to table 2, and the occurrences of table 2 without relation to table 1

example: select tabela1.* , tabela2.* from tabela1 full join tabela2 ON tabela1.id = tabela2.fk_tabela1

CROSS JOIN

Each row in table 1 is returned combined with each row in table 2

example: select tabela1.* , tabela2.* from tabela1 cross join tabela2

Your query should work the way you are simply adding DISTINCT in front of SELECT

But I believe that the ideal query is next to the following

    select distinct alunos.numero,faltas_licao.tipo from alunos 
    left join faltas_licao on faltas_licao.aluno_id = alunos.id_aluno 
    left join turma on alunos.id_turma = turma.id_turma 
    left join licao on licao.id_turma = turma.id_turma 
    left join faltas_licao on faltas_licao.id_disciplina = licao.id_disciplina 
    left join disciplinas on licao.id_disciplina = disciplinas.id_disciplina 
    where faltas_licao.licao = '" & currentlicao & "' 
    and disciplinas.nome = '" & F_Topo.CB_Disciplina.SelectedItem.ToString & "' 
    and turma.nome = '" & F_Topo.CB_Turma.SelectedItem.ToString & "' 
    and alunos.Numero = '" & aluno & "'"

And although your table only has 4 records, it returns more results because your query performs evaluations of WHERE for each of the tables that you included in FROM , re-evaluating the same data many times, and thus do not add DISTINCT in front of SELECT , it will return a row for each comparison that is answered, regardless of whether it has already been returned before.

Although you disagree with the explanation of the% s of% s by @DiegoValadares, because if you analyze the execution plan using INNER JOIN and WHERE, you will see that both have the same execution plan.

Syntactically I prefer JOIN , because this makes explicit my intention to link tables, I would use WHERE only if I was dealing with some old database, of those that use JOIN , after all, JOIN is not an option here.

But your problem here is to bind the ANSI-89 table to licao , to make this connection properly, you should compare all the columns that make up the primary key of faltas_licao , so your query would look like like this:

select 
    alunos.numero,
    faltas_licao.tipo 
from licao
join turma on licao.id_turma = turma.id_turma 
join disciplinas on licao.id_disciplina = disciplinas.id_disciplina 
join faltas_licao on 
    licao.id_turma = faltas_licao.id_turma and
    licao.id_disciplina = faltas_licao.id_disciplina and    
    licao.licao = faltas_licao.licao
join alunos on faltas_licao.aluno_id = alunos.id_aluno 
where 
    faltas_licao.licao = @licao and
    disciplinas.nome = @disciplina and
    turma.nome = @turma and
    alunos.Numero = @aluno

Another point, avoid concatenating strings when setting up your query, instead parameterize your query.

Dim sql As String = "" & _
    "select" & vbCrLf & _
    "   alunos.numero," & vbCrLf & _
    "   faltas_licao.tipo" & vbCrLf & _ 
    "from licao" & vbCrLf & _
    "join turma on licao.id_turma = turma.id_turma" & vbCrLf & _ 
    "join disciplinas on licao.id_disciplina = disciplinas.id_disciplina" & vbCrLf & _
    "join faltas_licao on" & vbCrLf & _ 
    "   licao.id_turma = faltas_licao.id_turma and" & vbCrLf & _
    "   licao.id_disciplina = faltas_licao.id_disciplina and" & vbCrLf & _  
    "   licao.licao = faltas_licao.licao" & vbCrLf & _
    "join alunos on faltas_licao.aluno_id = alunos.id_aluno" & vbCrLf & _ 
    "where" & vbCrLf & _ 
    "   faltas_licao.licao = @licao and" & vbCrLf & _
    "   disciplinas.nome = @disciplina and" & vbCrLf & _
    "   turma.nome = @turma and" & vbCrLf & _
    "   alunos.Numero = @aluno"

Dim sqlCom As New SqlCommand(sql, con3)
sqlCom.Parameters.AddWithValue("@licao", currentlicao)
sqlCom.Parameters.AddWithValue("@disciplina", F_Topo.CB_Disciplina.SelectedItem.ToString)
sqlCom.Parameters.AddWithValue("@turma", F_Topo.CB_Turma.SelectedItem.ToString)
sqlCom.Parameters.AddWithValue("@aluno", aluno)

One last tip would be to move this query to a licao , to do this, save your script as a Resource file and add it as your project's Resource.