Solved

Multiple column search form with SQL Query backend

Posted on 2014-01-17
2
293 Views
Last Modified: 2014-03-11
Hi,

My brain is fried after hours of trying to make this work. Any help would be appreciated.

I have a form with 6 search fields. Each search field can be used to query a table in my database to produce results. If a user is searching for John Smith with a SSN of 111-11-1111, the user can search for the last name Smith or for SSN 111% to produce a result. The client would be found in the database.

The form uses the following query to produce results based on the data in the fields at the time the search button is clicked.

select * 
from sus.newsusssntest2 
where 
lower(last_name) like lower(<<Last_Name>>)  or 
(s_s_n) like (<<SSN>>)  or 
(d_o_b) = (<<DOB>>)  or 
lower(medicaid) like lower(<<Medicaid>>)  or 
lower(medicare) like lower(<<Medicare>>)  or 
lower(t_a_bs_i_d) like lower(<<TABS_ID>>)  

Open in new window


If the user only knows one piece of information to search for the patient with, this query works fine. The problem occurs when the user knows more than one piece of information.

In this scenario there are two clients in the database named John Smith and a misc record.

John Smith 111-11-1111
John Smith 222-22-2222
Tim Allen 111-11-1111

(I understand that this is a bad example since SSN is unique)

If the user is looking for John Smith with SSN of 111-11-1111, the user would fill in the form with:

Last Name: Smith
SSN: 111%

and then user clicks search.

Since the query above is based on 'or' clauses, there is no exact result for the combination of Smith and 111%. Either search term Smith or 111% will produce 2 records even though there are two search terms available for the query to process.

I'm having trouble finding a way to have all columns cross reference each other so that if the user does search for Smith and 111% but leaves all of the other fields blanks, that the record for John Smith SSN:111-11-1111 shows as the only search result.

I hope this was clear enough. Please let me know your thoughts. Thanks.
0
Comment
Question by:susnewyork
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39789861
try this out

1) send null to the fields or columns where the user didn't give any information
select * 
from sus.newsusssntest2 
where 
lower(last_name) like CASE WHEN <<Last_Name>> IS NULL THEN lower(last_name) ELSE lower(<<Last_Name>>)  AND

s_s_n like CASE WHEN <<SSN>> IS NULL THEN s_s_n ELSE <<SSN>>  
AND

Open in new window


marry this approach to all he fields, and it should work for your scenario
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39790294
Hi,

If using a procedure
create procedure dbo.Search
	@LastName varchar( 30 )
	, @FirstName varchar( 30 )

as
	select *
	from sus.newsusssmtest2 tt
	where
		( @LastName is null or tt.LastName like @LastName )
		and ( @FirstName is null or tt.FirstName like @FirstName )
;

go

Open in new window

HTH
  David
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 71
Convert SP in a format for debugging 7 30
testing sql16 on win10 vs OS16 2 35
Sql Query Datatype 2 19
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now