Hvordan kobler man en ekstra tabel på sin sql søgning

Du kan gøre det på flere måder, men en løsning kunne være, istedet for en subselect ;

SELECT T1.Værdi, T2.Værdi
FROM Tabel 1 T1, Tabel2 T2
WHERE T1.Værdi = T2.Værdi

Så får du koblet en tabel mere på, og sætte nogle kriterier på...

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"

hvad gør sådan en INNER JOIN ?

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

løsningen blev en left join:

<%
Dim rsResults
Dim rsResults_numRows

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"

'response.write tsk_sql
rsResults.Source = tsk_sql

rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

kjulius
er du frisk på at dele point

Selvfølgelig...

tak ;)