How to make conditional where based on another column

Question

I want to make where clause conditional based on another column. I tried the below code but it gives syntax error

SELECT *
FROM   jobs
WHERE  CASE
         WHEN "jobs"."tags" IS NOT NULL
              AND "jobs"."isprivate" = 1 THEN (
         Concat(',', "jobs"."tags", ',') LIKE '%,Test,%'
          OR
         Concat(',', "jobs"."tags", ',') LIKE '%,natures_touch_fork_lift,%' )
         ELSE 1 = 1
       END;

In the above code, I want search tags only the job is private and the tag has data

Your help is much appreciated.

Could you provide some sample data and expect result? AND I think the problem is on OR which is after the THEN — D-Shih
– D-Shih, Commented May 20, 2022 at 3:04
FYI case is an expression not a statement - it returns a value, it doesn't allow you to conditionally run a statement. Just try standard AND/OR logic. I should also mention that this will be very bad for performance because by using the concat function on the column in your where clause you make the query unsargable, i.e. unable to use indexes. — Dale K
– Dale K, Commented May 20, 2022 at 3:04

Tim Biegeleisen · Accepted Answer · 2022-05-20 03:09:49Z

2

You don't need a CASE expression here actually:

SELECT *
FROM jobs
WHERE (isprivate = 1 AND (
           ',' + tags + ',' LIKE '%,Test,%' OR
           ',' + tags + ',' LIKE '%,natures_touch_fork_lift,%'
      )) OR isprivate = 0;

Note that you should avoid storing the tags as CSV. Instead, persist each tag value in a separate record. The above query should be viewed a temporary workaround and used long term only if you cannot control the table structure.

edited May 20, 2022 at 3:09

answered May 20, 2022 at 3:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dale K Over a year ago

Maybe when OP upgrades from SQL Server 2008 they can normalise their tables? :)

Collectives™ on Stack Overflow

How to make conditional where based on another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related