asked on

ASP SQL Join Query?

Hi,

This question is leading on from my other question:
https://www.experts-exchange.com/questions/20966025/ASP-SQL-Query.html

(a bit of background... :))

Basically, the above is a SQL keyword search what I need now is a SQL SELECT that can process a form with multi-form fields.

For example the search facility requires a Business record to be search by a few different fields, submitted by a form.

The fields are:
strSearch_Advanced_MembershipStatus = Request.QueryString("SA_MembershipStatus")
strSearch_Advanced_BSO = Request.QueryString("SA_BSO")
strSearch_Advanced_Borough = Request.QueryString("SA_Borough")
strSearch_Advanced_Region = Request.QueryString("SA_Region")
strSearch_Advanced_Gender = Request.QueryString("SA_Gender")
strSearch_Advanced_Age = Request.QueryString("SA_Age")
strSearch_Advanced_EthnicOrigin = Request.QueryString("SA_EthnicOrigin")
strSearch_Advanced_EmployeeNumber = Request.QueryString("SA_EmployeeNumber")
strSearch_Advanced_DirectoryClassification = Request.QueryString("SA_DirectoryClassification")

All are type Integer, except
Gender where the submitted paramerers are "male" or "female"

If a option is not changed, from default, then the submission is "AnyContent"

I now need to find a record that matches anyone of the above search criteria. The SQL SELECT line I have so far only does the first one, but am clueless on how to do the rest...?

What I already have:
Dim SQL_Search_Advanced
SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business "

SQL_Search_Advanced = SQL_Search_Advanced + "INNER JOIN Membership ON Business.ID = Membership.BusinessID "

If (strSearch_Advanced_MembershipStatus = "AnyContent") Then
SQL_Search_Advanced = SQL_Search_Advanced + "WHERE Membership.MembershipStatus <> 'blank' "
Else
SQL_Search_Advanced = SQL_Search_Advanced + "WHERE Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' "
End If

Any thoughts on how I can integrate the remaining search parameters into the SQL SELECT line?

Caspar

ZeeSparrow

I would continue on as you are.... with a few modifications.

DIM strWhere
strWhere = ""

SQL_Search_Advanced = "SELECT Business.ID,Business.BusinessName,Business.ICF,Business.CNA,Business.Title_Code,Business.Forename,Business.Surname,Business.Telephone, Membership.BusinessID,Membership.MembershipStatus,Membership.MembershipNumber,Membership.ExpiryDate,Membership.AppointedBSO FROM Business INNER JOIN Membership ON Business.ID = Membership.BusinessID WHERE "

If (strSearch_Advanced_MembershipStatus = "AnyContent") Then
strWhere = strWhere + "Membership.MembershipStatus <> 'blank' OR "
Else
strWhere = strWhere + "Membership.MembershipStatus = '"&strSearch_Advanced_MembershipStatus&"' OR "
End If

If (strSearch_Advanced_BSO = "AnyContent") Then
strWhere = strWhere & "Membership.BSO <> 'blank' OR "
Else
strWhere = strWhere & "Membership.BSO = '" & strSearch_Advanced_BSO &"' OR "
End If

... etc... then ....

IF RIGHT( strWhere, 4 ) = " OR " THEN strWhere = LEFT( strWhere, LEN( strWhere ) - 4 ) ' <------ to remove the trailing " OR "

SQL_Search_Advanced = SQL_Search_Advanced & strWhere

HTH

CasparUK

ASKER

I see what you are saying, but I think it's not pulling tha data I need...

If I'm reading it right then the data from the database will be

memberstatus = <whatever> or AnyContent
OR
BSO = <whatever> or AnyContent

What I need is the content to be memberstatus AND BSO

For example, here's three records:

1. John:
Memberstatus = Active
BSO = Fred
Borough = Cheshire

2. Adam:
Memberstatus = Inactive
BSO = Caspar
Borough = Surrey

3. Shaun:
Memberstatus = Active
BSO = Caspar
Borough = Derby

So if I did a search on these 3 criteria for:
Membership =Active
BSO=Caspar
Borough=AnyContent

Then I'd expect to only get back the record Shaun and not either of the other two like I think, your script would provide? Cause both non-default (not AnyContent) are satisfied, but only one record both are satisfied.

ASKER CERTIFIED SOLUTION

ZeeSparrow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ZeeSparrow

You will also have to account for when they choose AnyContent for all of the criteria.

Prior to the statement
SQL_Search_Advanced = SQL_Search_Advanced & strWhere

Add this

IF LEN(strWhere) = 0 THEN
SQL_Search_Advanced = LEFT( SQL_Search_Advanced , LEN( SQL_Search_Advanced ) - 7 ) '<--- remove the training " WHERE "
END IF

-------- OR ----------

add this

IF LEN(strWhere) = 0 THEN
strWhere = " 1=1 "
END IF

Either way you would return ALL records from the join.

CasparUK

ASKER

Excellent :D

That does the trick :)

Although, I chnaged the below line, it seemed to generate errors before

IF RIGHT( strWhere, 4 ) = " AND " THEN strWhere = LEFT( strWhere, LEN( strWhere ) - 5 ) ' <------ to remove the trailing " AND "

TO:
      If RIGHT( strWhere, 4 ) = "AND " THEN
            strWhere = LEFT( strWhere, LEN( strWhere ) - 4 )
      End If

Thanks again,
C

p.s. I've posted a related question here, if you fancy it? :)
https://www.experts-exchange.com/questions/20973246/ASP-SQL-Age-Query.html