CODEWITHSUNDEEP
sqlsql-server
WSilhavy
WSilhavy

Reputation: 13

Where clause when table is not null and returns a list of users and users that belong to same state

I'm looking to return values of customers that may be filtered if a custom list is set in the DB.

If the custom list is uploaded I want to write a query to return all customers in that list as well as any customers that belong to that state. Otherwise just return an unfiltered list if no list has been set.

This is what I wrote hoping that would accomplish my goal, but it is failing to filter if the list was uploaded and returns the same data if commented out :

SELECT U.userId
      ,U.State 
       FROM UsersAndStates U
       LEFT JOIN @CustomUsersAndState C -- variable table where custom list is stored for this query
       on U.userId= C.userId
WHERE U.userId LIKE CASE WHEN C.userId IS NOT NULL
                         THEN '%' + C.userId + '%'
                         WHEN C.userId IS NULL
                         THEN '%' + U.userId + '%'
                         WHEN C.State = U.State 
                         THEN '%' + U.userId + '%
                         END

Thank you for reading.

edit:fixed alias mistake

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your logic simplifies to:

SELECT U.userId, U.State 
FROM UsersAndStates U LEFT JOIN
     @CustomUsersAndState C -- variable table where custom list is stored for this query
     ON U.userId= C.userId
WHERE (C.UserId IS NOT NULL AND U.userId LIKE '%' + C.userId + '%') OR
      C.userId IS NULL

You might ask: "What happened to the state condition?" Well, c.userId is either NULL or not NULL, so the third condition in the CASE is never executed.

Generally, boolean expressions are considered easier to follow than CASE statements in a WHERE clause. At the very least, they are easier for the optimizer to handle.

If I understand you conditions correctly, you would seem to want:

SELECT U.userId, U.State 
FROM UsersAndStates U LEFT JOIN
     @CustomUsersAndState C -- variable table where custom list is stored for this query
     ON U.userId = C.userId
WHERE (C.UserId IS NOT NULL AND
       (U.userId LIKE '%' + C.userId + '%' OR
        U.state = C.state
       )
      ) OR
      C.userId IS NULL

I am unclear why you are using LIKE for the userId, when = would seem to suffice:

WHERE (C.UserId IS NOT NULL AND (U.userId = C.userId OR U.state = C.state)
      ) OR
      C.userId IS NULL

EDIT:

I think the logic you really want is:

SELECT U.userId, U.State 
FROM UsersAndStates U 
WHERE NOT EXISTS (SELECT 1 FROM @CustomUsersAndState C) OR
      EXISTS (SELECT 1
              FROM @CustomUsersAndState C
              WHERE U.userId LIKE C.userId OR U.state = C.state
             );

Upvotes: 1

Related Questions