29. september 2005 - 16:46Der er
6 kommentarer og 2 løsninger
Hvordan kobler man en ekstra tabel på sin sql søgning
Jeg har nedenstående sql men der er en fejl i 'vesselterminal'. For den værdi eksisterer ikke i den tabel jeg skal søge i men i stedet i den tabel som indeholder den bruger der oprettes en record med. Jeg kan koble brugertabellen på med værdien 'CivilRegistrationNumber' men ved ikke hvordan man gør. Håber i kan hjælpe.
Inspiration til løsning (aner ikke om det hjælper eller forvirrer :): AND CivilRegistrationNumber = (SELECT CivilRegistrationNumber FROM tblUsers WHERE vesselterminalID = " & Request.QueryString("vesselterminal") ")
SQL:
tsk_sql = "SELECT * FROM dbo.tblInjury WHERE (DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "
If Request.QueryString("vesselterminal")<>"" Then tsk_sql = tsk_sql & "AND vesselterminalID = " & Request.QueryString("vesselterminal") End if If Request.QueryString("jobposition")<>"" Then tsk_sql = tsk_sql & "AND jobpositionID = " & Request.QueryString("jobposition") & " " End if If Request.QueryString("injuryplace")<>"" Then tsk_sql = tsk_sql & "AND injuryplaceID = " & Request.QueryString("injuryplace") & " " End if If Request.QueryString("injurynature")<>"" Then tsk_sql = tsk_sql & "AND injurynatureID = " & Request.QueryString("injurynature") & " " End if If Request.QueryString("injurypart")<>"" Then tsk_sql = tsk_sql & "AND injurypartID = " & Request.QueryString("injurypart") & " " End if tsk_sql = tsk_sql & "ORDER BY " & Request.QueryString("orderby") & " ASC"
Mon ikke en inner join til tblUsers skulle kunne klare den sag:
tsk_sql = "SELECT * FROM dbo.tblInjury i INNER JOIN dbo.tblUsers u ON i.CivilRegistrationNumber = u.CivilRegistrationNumber WHERE (i.DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') AND (i.SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') "
If Request.QueryString("vesselterminal")<>"" Then tsk_sql = tsk_sql & "AND u.vesselterminalID = " & Request.QueryString("vesselterminal") End if If Request.QueryString("jobposition")<>"" Then tsk_sql = tsk_sql & "AND i.jobpositionID = " & Request.QueryString("jobposition") & " " End if If Request.QueryString("injuryplace")<>"" Then tsk_sql = tsk_sql & "AND i.injuryplaceID = " & Request.QueryString("injuryplace") & " " End if If Request.QueryString("injurynature")<>"" Then tsk_sql = tsk_sql & "AND i.injurynatureID = " & Request.QueryString("injurynature") & " " End if If Request.QueryString("injurypart")<>"" Then tsk_sql = tsk_sql & "AND i.injurypartID = " & Request.QueryString("injurypart") & " " End if tsk_sql = tsk_sql & "ORDER BY i." & Request.QueryString("orderby") & " ASC"
output er tom. hvis jeg lader mine søgefelter være tomme og udskriver sql ser den sådan ud: SELECT * FROM dbo.tblInjury i INNER JOIN dbo.tblCarUser u ON i.CivilRegistrationNumber = u.CivilRegistrationNumber WHERE (i.DateOfInjury between '01-01-2000' AND '10-12-2005') AND (i.SickDays between '0' AND '999') ORDER BY i.CivilRegistrationNumber ASC
Set rsResults = Server.CreateObject("ADODB.Recordset") rsResults.ActiveConnection = MM_claims_conn_STRING tsk_sql = "SELECT * FROM dbo.tblInjury LEFT JOIN dbo.tblEmployee ON dbo.tblInjury.CivilRegistrationNumber = dbo.tblEmployee.CivilRegistrationNumberE "
If Request.QueryString("vesselterminal")<>"" Then tsk_sql = tsk_sql & "AND vesselterminalID = " & Request.QueryString("vesselterminal") & " " End if If Request.QueryString("jobposition")<>"" Then tsk_sql = tsk_sql & "AND jobpositionID = " & Request.QueryString("jobposition") & " " End if
tsk_sql = tsk_sql & "WHERE (DateOfInjury between '" & ConvertToSqlDate(Request.Querystring("startdate")) & "' AND '" & ConvertToSqlDate(Request.Querystring("enddate")) & "') "
If Request.QueryString("min")<>"0" Then tsk_sql = tsk_sql & "AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') " Else If Request.QueryString("max")<>"999" Then tsk_sql = tsk_sql & "AND (SickDays between '" & (Request.Querystring("min")) & "' AND '" & (Request.Querystring("max")) & "') " End if End if
If Request.QueryString("injuryplace")<>"" Then tsk_sql = tsk_sql & "AND injuryplaceID = " & Request.QueryString("injuryplace") & " " End if If Request.QueryString("injurynature")<>"" Then tsk_sql = tsk_sql & "AND injurynatureID = " & Request.QueryString("injurynature") & " " End if If Request.QueryString("injurypart")<>"" Then tsk_sql = tsk_sql & "AND injurypartID = " & Request.QueryString("injurypart") & " " End if tsk_sql = tsk_sql & "ORDER BY " & Request.QueryString("orderby") & " ASC"
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.