how to filter data by two columns for full text search sql query

Question

0.00/5 (No votes)

See more:

Hi all,

i have a query which have multiple search options and i have apply the query something like this

SQL

SELECT * FROM TABLE WHERE 

((TABLE.COLUMN1 IS NULL OR TABLE.COLUMN1=0) AND (@variable1 IS NULL OR @variable1=0)
OR TABLE.COLUMN1= CASE WHEN ((@variable1=0) OR (@variable1 IS NULL))
THEN TABLE.COLUMN1 ELSE @variable1 END)

AND 

((TABLE.COLUMN2 IS NULL OR TABLE.COLUMN2=0) AND (@variable2 IS NULL OR @variable2=0)
OR TABLE.COLUMN2= CASE WHEN ((@variable2=0) OR (@variable2 IS NULL))
THEN TABLE.COLUMN2 ELSE @variable2 END)

now my issue is for column2 i need to check the same thing with two different columns. "COLUMN2" and "COLUMN3". if COLUMN2 is NULL it should check from COLUMN3 for same variable and if both are null it should get all the records.

we can take this link as a ref for your full understanding

https://ask.sqlservercentral.com/questions/15758/query-with-optional-parameters.html[^]

Posted 17-Nov-14 20:17pm

ravikhoda

Updated 17-Nov-14 22:01pm

v2

Add a Solution

Comments

Shweta N Mishra 18-Nov-14 3:43am

put some sample record and explain and what results you need, that may help understanding your issue in better way.

ravikhoda 18-Nov-14 3:59am

okay here is the thing.

there are two columns location1 and location2. both save the primary id of the location in particular table. now the thing is that if there is no search for location it should bring all the data, if user search by location then it will first check the location1 column in the database if that is null it should check for location2 in the same row in database and get the records based on that. can't display data as it has lot of columns and fields.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-11-17T22:04:00

Solution 1

you could put some sample records, entirre data is not required but based on what i have understood you can do something like this

SQL

If Isnull(@searchText,'')
   select * from yourtable
Else
   select * from yourtable 
     where isNull(Location1,Location2)=@searchText

Posted 17-Nov-14 22:04pm

Shweta N Mishra

Updated 17-Nov-14 22:05pm

v2

Comments

ravikhoda 18-Nov-14 4:36am

thanks for your answer. this is not the exact thing which i needed but somehow i manage to get the actual out put based on the things that you have mentioned.