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.
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>>)
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
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.