基于匹配的标准搜索 [英] Search based on matched criteria

问题描述

我用下面的查询返回的所有记录，其中至少有2个条件匹配（<一个href=\"http://stackoverflow.com/questions/1405308/sql-query-to-find-matching-values-based-on-user-input\">provided通过Quassnoi ）。

I'm 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

问题是，它似乎工作为街和镇搜索时（返回请求的街道，镇的所有匹配属性），而不是街和邮政code（返回任何结果）搜索时。要获取街和邮政code的工作（返回结果）搜索，我不得不删除以下行;

The problem is that it seems to work when searching for Street and Town (returns all matching properties with the requested street and town), but not when searching for Street and Postcode (returns no results). To get the search for Street and Postcode to work (returning results), I had to remove the following lines;

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

但后来因为我已经删除了上述4条线路，以获得街和邮政code搜索工作显然是城区和邮政code或镇和街道的搜索不工作。

But then obviously the Town and Postcode or Town and Street searches don't work because i had removed the above 4 lines to get the Street and Postcode search to work.

我想知道，如果有人可以提供一些帮助呢？

I wondered if anyone could offer some help with this?

感谢您。

推荐答案

我不知道，你应该强制执行至少两个条件标准倒在数据库中你应该永远不会有知识，其中两人已经。填写也许不是，这可能为你工作 - 这是一个模式我经常使用，并应与标准的任意组合（我假设这是一个存储过程里面！）应对：

I'm not sure you should be enforcing the 'at least two conditions' criteria down in the database as you should probably never have knowledge of which two have been filled in. Perhaps instead this might work for you - it's a pattern I use quite often and should cope with any combination of criteria (I'm assuming this is inside a stored proc!):

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

要从你的ASP页面中调用这个你会想一些code这样的事情（这的可以的需要一点调试，我ADO和放大器; VBScript中的ASP是pretty生锈）：

To call this from your ASP page you'll want some code something like this (this may need a bit of debugging, my ADO & VBScript for ASP is 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 ought to work for any combination of parameters, whether you enter none, some or all of them!

这篇关于基于匹配的标准搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！