Har tre tabeller. Member innehåller medlemsinfo, yrke_koppling kopplar yrken till medlemen via memberID och licence kopplar på samma sätt körkort.
Det ska alltså vara en sökfunktion där man kan söka på namn, stad, ålder, yrke och körkort. Man ska kunna fylla i vilka man vill. Det funkar dock inte.
<code>
Visa ="SELECT * FROM member " & _
"LEFT JOIN yrke_koppling ON member.MemberID = yrke_koppling.MemberID " & _
"WHERE Namn LIKE '%" + Replace(varNamn, "'", "''") + "%' " & _
"AND Stad LIKE '%" + Replace(varStad, "'", "''") + "%' " & _
"AND BirthYY BETWEEN " & varAge2 & " AND " & varAge & " " & _
"AND Task = " & request.form("Yrke") & " " & _
"ORDER BY Namn DESC"
</code>
Denna bit är enbart kopplingen mellan member och yrke_koppling. Trixet är att om medlemmen inte har något yrke så får jag inget resultat. Finns det nån wildcard för numeriska värden?

Sen så blir det ju ännu trixigare när jag ska lägga till kopplingen till licence:
<code>
Visa ="SELECT * FROM member " & _
"LEFT JOIN yrke_koppling ON member.MemberID = yrke_koppling.MemberID " & _
"LEFT JOIN licence ON member.MemberID = licence.MemberID " & _
"WHERE Namn LIKE '%" + Replace(varNamn, "'", "''") + "%' " & _
"AND Stad LIKE '%" + Replace(varStad, "'", "''") + "%' " & _
"AND BirthYY BETWEEN " & varAge2 & " AND " & varAge & " " & _
"AND Task = " & request.form("Yrke") & " " & _
"AND LicenceID = " & request.form("LicenceID") & " " & _
"ORDER BY Namn DESC"
</code>

Då får jag Syntax error (missing operator) in query expression 'member.MemberID = yrke_koppling.MemberID LEFT JOIN licence ON member.MemberID = licence.MemberID'.

Försök med:
<code>
Visa ="SELECT *" & vbCrLf & _
"FROM (member LEFT JOIN " & vbCrLf & _
" yrke_koppling ON member.MemberID = yrke_koppling.MemberID) LEFT JOIN " & vbCrLf & _
" licence ON member.MemberID = licence.MemberID " & vbCrLf & _
"WHERE Namn LIKE '%" + Replace(varNamn, "'", "''") + "%' AND " & vbCrLf & _
" Stad LIKE '%" + Replace(varStad, "'", "''") + "%' AND " & vbCrLf & _
" BirthYY BETWEEN " & varAge2 & " AND " & varAge & " AND " & vbCrLf & _
" (Task Is Null OR Task = " & request.form("Yrke") & ") AND " & vbCrLf & _
" (LicenceID Is Null OR LicenceID = " & request.form("LicenceID")) & " & vbCrLf & _
"ORDER BY Namn DESC"
</code>

Tycker det borde funka men jag får
Expected end of statement
/community/member_search.asp, line 54, column 65
" (LicenceID Is Null OR LicenceID = " & request.form("LicenceID")) & vbCrLf & _

Vad är galet?

Jag löste det på ett annat sätt. Genom att kolla allt i if-satser så sätter jag ihop select-satsen efter det. Löser också problemen med att man får flera av samma resultat pga LEFT JOIN.
Men är det här bästa sättet att lösa det? Ska man tänka annorlunda?
<code>
<% IF Request.QueryString("DO") = "SOK" THEN
Dim SQLstring, varAge, varAge2

SQLstring = "SELECT * FROM (member LEFT OUTER JOIN " & vbCrLf & _
" yrke_koppling ON member.MemberID = yrke_koppling.MemberID) LEFT OUTER JOIN " & vbCrLf & _
" licence ON member.MemberID = licence.MemberID WHERE Member.MemberID > 0 "

if request.form("Yrke") <> "" AND request.form("LicenceID") <> "" then
SQLstring = "SELECT * FROM (member LEFT OUTER JOIN " & vbCrLf & _
" yrke_koppling ON member.MemberID = yrke_koppling.MemberID) LEFT OUTER JOIN " & vbCrLf & _
" licence ON member.MemberID = licence.MemberID WHERE Member.MemberID > 0 "

elseif request.form("Yrke") <> "" then
SQLstring = "SELECT * FROM (member LEFT OUTER JOIN " & vbCrLf & _
" yrke_koppling ON member.MemberID = yrke_koppling.MemberID) " & vbCrLf & _
" WHERE Member.MemberID > 0 "

elseif request.form("LicenceID") <> "" then
SQLstring = "SELECT * FROM (member LEFT OUTER JOIN " & vbCrLf & _
" licence ON member.MemberID = licence.MemberID) " & vbCrLf & _
" WHERE Member.MemberID > 0 "

else
SQLstring = "SELECT * FROM member WHERE Member.MemberID > 0 "
end if

if Request.Form("Namn") <> "" then
SQLstring = SQLstring & "AND Namn LIKE '%" &_
Request.Form("Namn") & "%'"
end if

if request.form("Stad") <> "" then
SQLstring = SQLstring & " AND Stad LIKE '%" &_
request.form("Stad") & "%'"
end if

if request.form("Age") <> "" then
dDate = Date()
Idag = Right(Year(dDate),4)
varAge = Idag - request.form("Age")
varAge2 = Idag - request.form("Age") - 9
SQLstring = SQLstring & " AND BirthYY BETWEEN " & varAge2 & " AND " & varAge & " "
end if

if request.form("Yrke") <> "" then
SQLstring = SQLstring & " AND Task = " & request.form("Yrke") & " "
end if

if request.form("LicenceID") <> "" then
SQLstring = SQLstring & " AND LicenceID = " & request.form("LicenceID") & " "
end if
SQLstring = SQLstring & " ORDER BY Namn DESC"
rsMember.Open SQLstring, Connect, adOpenStatic, adLockOptimistic
%>
</code>