Newbie: SQL query to perform searches on multiple fields

[NotesUser]

I am trying to produce a search form that allows the user to enter up to 4 fields, namely:
Title, First Name, Last Name and City

Scenarios:
- if the user only enter the title (e.g. Mr) and leave all other fields blank, then the result should return all records for the Title = "Mr"

- if the user enters Title="Mr" and City="New*" and leave all other fields blank, then all people with a title of Mr and living in a city that starts with New will be displayed

and so on...

How to generate this query??

 

[fredericofonseca]

search your database manuals and these forums for

"select" and "where" and "like"

and you will find plenty of examples

Off course you will need to build your SQL string, and this will depend on the language you are using for that. e.g. if VB it's one thing, if COBOL it is another, if asp or php even others, and for that bit it is better to post on your specific language forum.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[NotesUser]

This is my code so far:
SELECT [Person].* FROM [Person]
WHERE
(@Title IS NULL OR [Person].[Title] = @Title)
AND
(@Family_Name IS NULL OR [Person].[Family_Name] = @Family_Name)
AND
(@Given_Name IS NULL OR [Person].[Given_Name] = @Given_Name)
AND
(@City IS NULL OR [Person].[City] = @City)

 

[r937]

use the following pseudocode to build your string before executing it

select [Person].* from [Person]
where 1=1
if Title entered
and [Person].[Title] = @Title endif
if Family_Name entered
and [Person].[Family_Name] = @Family_Name endif
if Given_Name entered
and [Person].[Given_Name] = @Given_Name endif
if City entered
and [Person].[City] = @City endif

r937.com | rudy.ca