Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Marin, CA
    Posts
    1

    Unanswered: SQL for searching on many fields when only some have values

    I'm trying to build a "search screen" where a user can enter a lot of detailed information or just a couple items. They will be searching on a database of people, so there are about 20-30 different fields they might fill out.

    My question is, what is the most efficient way to query the database when I don't know in advance which fields will have values and which will be left blank? If a field is left blank, I want to match ALL values in that column.

    I assume I could, using string manipulation in the code, build an SQL query built from a series of concatenated AND clauses, but ideally I'd like this to be in an Oracle stored procedure to which I will always pass all the values the user enters on the screen.

    I'm sure this problem has been solved (or at least approached) a million times before, so I'd like to learn from those who have ventured ahead of me...

    Thanks,

    DT

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wrote an article on how to approach this problem:

    The "any" option in dynamic search SQL
    (registration may be required, but it's free)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And for an example using the concatenated AND's:

    strSQL = "SELECT * MyTable WHERE (1=1)"
    If MyFirstListBox <> "All" Then
    strSQL = strSQL & " AND (MyType='" & Request.Form("MyFirstListBox") & "')"
    End If
    If MySecondListBox <> "All" Then
    strSQL = strSQL & " AND (MyDetail='" & Request.Form("MySecondListBox") & "')"
    End If
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •