SQL Query for Multiple Column filter using ( WHERE.. IN)  and "CONCAT" function:


Lets say we have a table as follows:

GroupID	DepartmentID	UserId	State	Name
100	201	111	NJ	Krishna
101	202	222	CT	Dhakal
102	203	333	PA	John

Now, lets say I want value of  "Name" column   based on four filters (GroupID, DepartmentID,UserId,State). Here is the query for that:

string query =
@" SELECT Distinct Name FROM  "Table Name"

 WHERE concat(concat(concat(GroupID, concat('-', DepartmentID)),  concat('-', UserId)),    (concat('-', Name))) in ('100-201-111-NJ,101-202-222-CT');

                     
The beauty of this query is that you can pass as many value as you want on above query after "in" statement. If you pass this query on any .Net application, it will return the DataTable  as below:

Name
Krishna
Dhakal

Tag: SQL Query, Sharepoint

~ Happy Coding..