SQL Server Filtering more than one column with more than one column in where clause

4bbkushb 于 2023-03-22 发布在其他

关注(0)|答案(3)|浏览(170)

I came across a scenario where I need to filter rows based on a combination of values. Let me explain using an example:

I have 2 tables as shown here:

For easy structuring I am including the T-SQL statements:

create table employees
(
    empId int,
    empname varchar(100),
    empSalary money
)

insert into employees 
values (1, 'John', 8000), (2, 'Sam', 9800), (3, 'Ray', 9500)

create table empLocations
(
    locId int,
    empId int
)

insert into empLocations 
values (1, 1), (2, 1), (3, 2), (4, 3), (5, 1)

I wish to get (without cross joins) the rows of employees who stays in location 1 and 4 (locIds).

In this example it should return John and Ray.

Please note there are many conditions I am checking here and the location wise filtering is optional. Hence the structure of query should be

declare @locationIds varchar(100) = '1,4'

select * 
from employees 
where 
    (0 = case when (@locationIds is null) then 0 else 1 end) 
     or 
     ---<here goes the condition>-----  in (select strg from [dbo].[SplitStringi] (@locationIds,',') ) 
    )

Please note in the above example I have used a function to return values from comma separated values as below

CREATE FUNCTION [dbo].[SplitStringi]       
    (@str nvarchar(4000),       
     @separator char(1))      
RETURNS TABLE
AS      
   RETURN (      
       WITH tokens(p, a, b) AS 
       (
           SELECT
               1, 1,       
               CHARINDEX(@separator, @str)      
           UNION ALL
           SELECT
               p + 1, b + 1,       
               CHARINDEX(@separator, @str, b + 1)      
           FROM tokens      
           WHERE b > 0      
        )      
        SELECT
            p indx,      
            SUBSTRING(@str, a,       
                      CASE WHEN b > 0 THEN b-a ELSE 4000 END) AS strg      
        FROM tokens      
      )

sql-server

来源：https://stackoverflow.com/questions/75775052/filtering-more-than-one-column-with-more-than-one-column-in-where-clause

3条答案

按热度按时间

ncgqoxb01#

Since you're using SQL Server 2019, you can use the built-in STRING_SPLIT function instead of a custom function to split strings.

This refactored example uses STRING_SPLIT and simplifies the check for the optional parameter. Additionally, the OPTION(RECOMPILE) hint will eliminate the IN predicate from the query plan at compile time when the location list is NULL .

DECLARE @locationIds varchar(100) = '1,4';

SELECT * 
FROM employees AS e
WHERE 
    @locationIds is null
    OR e.empId IN (
        SELECT el.empId
        FROM empLocations AS el
        JOIN STRING_SPLIT(@locationIds,',') AS locs ON locs.value = el.locId
        WHERE el.empId = e.empId
    )
OPTION(RECOMPILE);

赞(0）回复(0）举报 2023-03-22

y3bcpkx12#

I got it

declare @locationIds varchar(100)='1,4'
select * from employees 
where
(
(0 = CASE WHEN (@locationIds is null) THEN 0 ELSE 1 END) 
 or 
(employees.empId  in
 (select empid from  empLocations where locId in( select strg from [dbo].[SplitStringi] (@locationIds,',') ))
)
)

赞(0）回复(0）举报 2023-03-22

r3i60tvu3#

You can do it as follows :

select e.* 
from employees e
inner join empLocations el on e.empId = el.empId
where 
    (0 = case when (@locationIds is null) then 0 else 1 end) 
    or 
    (el.locId in (select value from string_split(@locationIds, ','))
     and el.locId in (select value from string_split(@locationIds, ','))
    );

Here is a working demo in SQL Server 2019

This gives you expected output as :

|empId| empname |empSalary |
|1    |  John   |  8000.00 |
|3    |  Ray    |  9500.00 |

Here is a more simplified query :

declare @locationIds varchar(100) = '1,4'

    SELECT e.*
    FROM employees e
    INNER JOIN empLocations el ON e.empId = el.empId
    WHERE @locationIds IS NULL OR el.locId IN (SELECT value FROM STRING_SPLIT(@locationIds, ','));

Working demo using DBFiddle

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Filtering more than one column with more than one column in where clause

3条答案

相关问题

热门标签

最新问答