Clever Geek Handbook

Search by agreed criteria

I am using the following query to return all records where at least 2 conditions match ( provided by Quassnoi ).

SELECT  *
FROM    (
        SELECT  ContentID
        FROM    (
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentStreet = 'Holderness Road'
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentTown = 'Hull'
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentPostCode = 'HU'
                ) qi
        GROUP BY
                ContentID
        HAVING  COUNT(*) >= 2
        ) q
JOIN    VWTenantPropertiesResults r
ON      r.ContentID = q.ContentID
WHERE   ContentBedrooms BETWEEN 1 AND 4
        AND ContentPrice BETWEEN 50 AND 500
ORDER BY
        ContentPrice

The problem is that it works when looking for Street and Town (returns all matching properties with the requested street and city), but not when looking for Street and Postcode (returns no results). To find Street and Postcode working (returning results) I had to remove the following lines:

        UNION ALL
        SELECT  id
        FROM    VWTenantPropertiesResults
        WHERE   ContentTown = 'Hull'

But then obviously searching in Town, Postcode or Town and Street doesn't work because I removed the above 4 lines to find Street and Postcode work.

I was wondering if anyone can help with this?

Thank.

0


source to share


4 answers


I'm not sure if you should apply at least two conditions in the database, since you will probably never know which ones were filled. Maybe it might work for you instead - this is a pattern I use quite often and should be able to handle any combination of criteria (I assume it's inside a stored procedure!):

DECLARE PROCEDURE PropertyList
@StreetName NVARCHAR(50) = NULL,
@Town NVARCHAR(50) = NULL,
@Postcode NVARCHAR(10) = NULL
AS

SET NOCOUNT ON

SELECT 
    *
FROM 
    VWTenantPropertiesResults
WHERE
    ContentBedrooms BETWEEN 1 AND 4
AND
    ContentPrice BETWEEN 50 AND 500
AND
    (@ContentStreet IS NULL OR ContentStreet = @ContentStreet)
AND 
    (@ContentTown IS NULL OR ContentTown = @ContentTown)
AND
    (@ContentPostcode IS NULL OR ContentTown = @ContentTown)
ORDER BY
    ContentPrice

To call this from an ASP page, you'll want some code to be something like this (this might require some debugging, my ADO and VBScript for ASP are pretty rusty!):



Dim cnn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim prmStreet 'As ADODB.Parameter
Dim prmTown 'As ADODB.Parameter
Dim prmPostcode 'As ADODB.Parameter
Dim rstProperty 'As ADODB.RecordSet
Dim i 'As Integer

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.ConnectionString = MyConnectionString

Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = cnn

'Set the CommandText property to the name of the stored proc we want to call
cmd.CommandText = "PropertyList"
cmd.CommandType = 4 'or adCmdStoredProc if you're using ADOVBS.inc

If Request.Form("StreetTextBox") = "" Then
    'No street entered so don't pass it to the stored proc
Else
    'A street has been entered so create a parameter...
    Set prmStreet = cmd.CreateParameter("@StreetName", 203, 1, 50, Request.Form("StreetTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmStreet)
End If

If Request.Form("TownTextBox") = "" Then
    'No town entered so don't pass it to the stored proc
Else
    'A town has been entered so create a parameter...
    Set prmTown = cmd.CreateParameter("@Town", 203, 1, 50, Request.Form("TownTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmTown)
End If

If Request.Form("PostcodeTextBox") = "" Then
    'No postcode entered so don't pass it to the stored proc
Else
    'A postcode has been entered so create a parameter...
    Set prmPostcode = cmd.CreateParameter("@Postcode", 203, 1, 10, Request.Form("PostcodeTextBox"))

    ' and add it to the Parameters collection of the Command object
    cmd.Parameters.Add(prmPostcode)
End If

cnn.Open

'This is the line that'll actually call the stored procedure
Set rstProperty = cmd.Execute()

cnn.Close

If rstProperty.BOF And rstProperty.EOF Then
    'If BOF And EOF are true then this is an empty recordset - we got no records back
    Response.Write "No records returned"
Else
    'We have records so write them out into a table
    Response.Write "<table><tr>"
    For i = 0 To rstProperty.Fields.Count - 1
        Response.Write "<td>"
        Response.Write rstProperty.Fields(i).Name
        Response.Write "</td>"
        Response.Write "<td>&nbsp;</td>"
    Next

    Response.Write "</tr>"

    Do While rstProperty.Eof = False
        Response.Write "<tr>"
        For i = 0 To rstProperty.Fields.Count - 1
            Response.Write "<td>"
            Response.Write rstProperty.Fields(i).Value
            Response.Write "</td>"
        Next
        Response.Write "<td>"
        Response.Write "<a href='ViewDetails.asp?id='" & rstProperty.Fields("PropertyId").Value & "'>View Details for this property</a>"
        Response.Write "</td>"
        Response.Write "</tr>"
        rstProperty.MoveNext
    Loop

    Response.Write "</table>"
End If

This should work for any combination of parameters, whether you enter them, some or all of them!

0


source


The query approach looks correct.

What do you mean if you don't work? Is it throwing an error or not returning any results or unexpected results?



Do you expect to use the full zip code? I see your example is using HU, not sure what this refers to. It doesn't look like a postcode for Canada, what region are you looking for?

Can you show us your dataset?

0


source


If you are correct, you say that this request:

SELECT  ContentID
FROM    VWTenantPropertiesResults
WHERE   ContentStreet = 'Holderness Road'
UNION ALL
SELECT  ContentID
FROM    VWTenantPropertiesResults
WHERE   ContentTown = 'Hull'
UNION ALL
SELECT  ContentID
FROM    VWTenantPropertiesResults
WHERE   ContentPostCode = 'HU'

returns less than 2 rows for some ContentID, whereas this query:

SELECT  ContentID
FROM    VWTenantPropertiesResults
WHERE   ContentStreet = 'Holderness Road'
UNION ALL
SELECT  ContentID
FROM    VWTenantPropertiesResults
WHERE   ContentPostCode = 'HU'

returns 2 or more for the same ContentID.

It would seem logically impossible, unless your DBMS has a big bug! For some ContentID that detects the problem, what are the above requests returning?

0


source


You probably want:

SELECT  *
FROM    (
        SELECT  ContentID
        FROM    (
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentStreet LIKE '%Holderness Road%' -- Take off the leading % if you can
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentTown LIKE '%Hull%' -- Take off the leading % if you can
                UNION ALL
                SELECT  ContentID
                FROM    VWTenantPropertiesResults
                WHERE   ContentPostCode LIKE '%HU%' -- Take off the leading % if you can
                ) qi
        GROUP BY
                ContentID
        HAVING  COUNT(*) >= 2
        ) q
JOIN    VWTenantPropertiesResults r
ON      r.ContentID = q.ContentID
WHERE   ContentBedrooms BETWEEN 1 AND 4
        AND ContentPrice BETWEEN 50 AND 500
ORDER BY
        ContentPrice

Since there is no postal code in the UK = 'HU'

it will look like 'HU_ ___'

so you need wildcards.

We usually restrict the search to match the beginning of the string (using search indexes), but sometimes users want arbitrary searches.

0


source






More articles:


All Articles