Strange Comic Book Behavior - Too Many Records

Asked

Viewed 173 times

0

The problem is that this returns me 8 records while in the database there are only 2. That is to say 4 for each 1. I cannot understand why. (If you want to edit the title of the question)

I got this comic:

Diagrama da base de dados.

And I have this select:

select alunos.numero,faltas_licao.tipo 

from turma,alunos,disciplinas,licao,faltas_licao 

where faltas_licao.aluno_id = alunos.id_aluno 

and licao.id_turma = turma.id_turma 

and faltas_licao.id_disciplina = licao.id_disciplina 

and licao.id_disciplina = disciplinas.id_disciplina 

and 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 & "'"

Here is the code "all": 

 Dim con3 As New SqlConnection(cs)
            Dim currentlicao As Integer = get_licao()
            'MsgBox("current: " & currentlicao)
            Using con3
                Dim aluno As Integer = linha.Index

                Dim sql As String = "select alunos.numero,faltas_licao.tipo from turma,alunos,disciplinas,licao,faltas_licao where faltas_licao.aluno_id = alunos.id_aluno and licao.id_turma = turma.id_turma and faltas_licao.id_disciplina = licao.id_disciplina and licao.id_disciplina = disciplinas.id_disciplina and 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 & "'"
                'MsgBox(sql)
                Dim sqlCom As New SqlCommand(sql, con3)
                con3.Open()
                Dim dr3 As SqlDataReader = sqlCom.ExecuteReader()

                Dim miau As Integer = 0


                If dr3.HasRows Then
                    While dr3.Read()
                        miau = miau + 1
                        'F_Principal.DataGridView2.Rows.Add(F_Principal.DataGridView2.RowCount + 1, dr3.Item(0))
                        MsgBox(miau)
                        MsgBox("ID: " & dr3.Item(0) & "TIPO:" & dr3.Item(1))
                    End While
                Else
                    ' Aqui faça o que quiser caso não tenha linha '
                End If
                dr3.Close()

Data in the database:

Dados

I do not understand how he returns me four records(alias 8 records, 4 being repeated).

Someone with database experience can detect my error?

EDIT:

Error:

System.Data.Sqlclient.Sqlexception (0x80131904): The Objects "faltas_licao" and "faltas_licao" in the FROM clause have the same Exposed Names. Use correlation Names to distinguish them. in System.Data.Sqlclient.SqlConnection.Onerror(Sqlexception Exception, Boolean breakConnection, Action1 wrapCloseInAction) em System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) em System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) em System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in System.Data.Sqlclient.SqlDataReader.Tryconsumemetadata() in System.Data.Sqlclient.SqlDataReader.get_MetaData() em System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) em System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) in System.Data.Sqlclient.SqlCommand.Executereader(Commandbehavior behavior, String method) in System.Data.Sqlclient.SqlCommand.Executereader() F_container.get_faults() in C: Users Alunop Desktop Work module 16 Work mod 16 Work mod 16 Work mod 16 F_container.Vb:line 163 ClientConnectionId:03d47535-a3d9-4723-b704-2649901d693e Error Number:1013,State:1,Class:16

sql-server vb.net

  • Your query is wrong. Try using some INNER JOIN.

  • https://www.youtube.com/watch?v=468_48aSz68 - I will use this as a reference.

  • And thank you :)...

  • @gmsantos, replace a JOIN inplicito for an explicit will not correct the query let alone improve the performance, the only gain here would be readability.

  • @lemario, could you say what the intention of the consultation is? would you like to take the total of students' absences? whether per student or class? in any case, I advise you not to assemble your query concatenating strings, parametrize the same.

  • @Tobymosque What I intend to do is to certain Tuma, discipline, lyco and student receive the type of foul, to then put in a table the fouls for each student.

Show 1 more comment

2 answers

2

You must join the tables using JOINS and not by WHERE

the 5 main types of JOIN are :

INNER JOIN

Returns a row for each occurrence of a given relation between 2 tables

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

LEFT JOIN

Regardless of the relation to be met , returns all lines of Tabela1 . If the relation is met it also returns the data from table 2 , otherwise, the data from Table 1 are filled and the data from table 2 receive NULL

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

RIGHT JOIN

Regardless of the relation to be met , returns all lines of the table2 . If the relation is met it also returns the data of Tabela1 , otherwise , the data of the table2 are filled and Tabela1 receive NULL

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

FULL JOIN

Returns the relations met and both the occurrences of table 1 unrelated to table 2 , and the occurrences of table 2 unrelated 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 the SELECT

But I believe 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 the WHERE for each of the tables you included in FROM, reassessing the same data many times ,  and thus not adding DISTINCTin front of the SELECT, it will return a line for each comparison that is met , independent of having been returned before .


							

							

								
								


							

						

						




    
	
	
	
  • 
		
    
		
    
			
    
				
    As a complement to the "theory" of JOINS, this answer that explains very well and very well each of the variations of JOINS.
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    +1 by the joins.
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    Thanks for the answer. I entered your code and it doesn’t work. If you put distinct in mine, you do what I want. What are the benefits of using left Join instead of just using distinct?   .
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    The main advantages I see are 1) Join is the standard for joining tables and you will hardly see code using WHERE to do this. 2) Gain in flexibility and ease to read and write the querys. 3)  Although @Tobymosque said that both have the same exec plan, I believe the only way this is possible is with the compiler understanding that both querys are equivalent but this way , you are limited to the understanding of the compiler and cannot force a different exec plan. ex: vc can focus "merge, loop... Join" and not "merge, loop... Where"
    
			
    
			 
		
    
	
    • 
	
	






					

					
					
					

						

							
							
2

							
							
						

						

							

								
Although I disagree with the explanation about JOINs given by @Diegovaladares, because if you analyze the execution plan when using INNER JOIN and WHERE, you will see that both have the same execution plan.


syntactically prefer the JOIN, because this makes explicit my intention to connect the tables, I would use the WHERE only if I was dealing with some old database, of those who use ANSI-89, After all JOIN is not an option here.


But your problem here is the table link licao with faltas_licao, to make this connection properly, you must compare all the columns that make up the primary key of licao, then your consultation would look 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 assembling your query, instead parametrize 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 Resource, for this, save your script as a file *.sql and add the same as your project’s Resource.


							

							

								
								


							

						

						




    
	
	
	
  • 
		
    
		
    
			
    
				
    Are you sure you both have the same execution plan ? I really didn’t know about it, I figured not because unless you explicitly state what kind of Join ( loop , merge or hash)  it tries to accomplish what it understands will generate the best execution plan , and also if you don’t force the order of the joins , bd makes the order of the junctions according to what " understands " that will generate the best execution plan
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    Diego, the type of Join will influence the execution plan, but in this specific case he is using an implicit Join, as was specified in ANSI SQL-89, it is worth remembering that the operators *=, =*, *=*, which are respectively the LEFT JOIN, the RIGHT JOIN and the FULL JOIN were discounted.
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    1
    
		
    
			
    
				
    @Diegovaladares, but don’t get me wrong, I advocate the use of JOIN explicitly to the detriment of JOIN implicit, although there is no performance gain, there is a huge readability gain. if you want to read more about it, read the introduction of the following link: SQL SERVER - What is the Difference Between An INNER JOIN and WHERE Clause
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    Yes, I have researched whether a Join is faster than a Where and in the stack in English the best answer is: "Theoretically, no, it should not be any faster. The queries optimizer should be able to generate independent execution plans for both. However, some BD systems can generate better execution plans for one of them (it will hardly happen for simple queries but for complex ones). You should test the 2 and see (In your BD system)."  http://stackoverflow.com/a/1129938
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    @Diegovaladares, the answer in question has a wider scope, in the example above we have a specific DBMS, in the case of Sql-Server.
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    It always generates the same execution plan independent of query complexity ? @Tobymosque
    
			
    
			 
		
    
	
    • 
	
	
	
	
  • 
		
    
		
    
			
    
				
    Let’s go continue this discussion in chat.
    
			
    
			 
		
    
	
    • 
	
	



Show 2 more comments



					

					
				


				
Browser other questions tagged sql-server vb.net 

				You are not signed in. Login or sign up in order to post.