CODEWITHSUNDEEP
sqlsql-server
Mowgli
Mowgli

Reputation: 3512

Can I use a CASE expression within a WHERE clause?

I am not sure how to do write query in sql.
This is what I have tried so far.

where
  case 
       when a.book_id like 'AB%' then a.book_id = b.school_id,   --1   
       when a.book_id like 'CB%' then a.book_id = b.college_id.  --2
  end


case 1 and 2 explanation.

1- I believe is good.
2- when a.book start with letter CB e.g CBQ123 then just take Q123 = b.college_id.

b.college_id don't have CB in front of it.

Edit to add example

select 
       a.Name,
       a.ID,
       a.Due,
       b.school_id,
       b.college_id
from Student a and FinishedStudent b
where
  case 
       when a.book_id like 'AB%' then a.book_id = b.school_id,   --1   
       when a.book_id like 'CB%' then a.book_id = b.college_id.  --2
  end

if a.book = CBQ111 then Q111 is in FinishedStudent table not CBQ11,so I need to just compare last 3 character.

edit with case 2 example

   when a.book_id ='CBQ111' then a.book_id(Q111) = b.college_id.  --2

Upvotes: 2

Views: 544

Answers (2)

wtjones
wtjones

Reputation: 4160

Edit: to correct an issue and clean up code.

You can put it in the WHERE clause but I prefer to put it in the join.

select 
       a.Name,
       a.ID,
       a.Due,
       b.school_id,
       b.college_id
from Student a
  join FinishedStudent b on a.book_id = 
    case 
       when a.book_id like 'AB%' then b.school_id,   --1   
       when a.book_id like 'CB%' then b.college_id.  --2
    end

Upvotes: 0

anon
anon

Reputation:

Just a minor syntax issue. In T-SQL, CASE is an expression that returns a value, so you need to compare the output to something - it is not a control-of-flow statement like it is in other languages like VB.

where a.book_id = case 
       when a.book_id like 'AB%' then b.school_id    --1   
       when a.book_id like 'CB%' then b.college_id   --2
  end

Based on the scrambling of word problems added in comments, perhaps what you're actually after is this:

WHERE (a.book_id LIKE 'AB%' AND a.book_id = b.school_id)
   OR (a.book_id LIKE 'CB%' AND SUBSTRING(a.book_id, 3, 255) = b.college_id)

There are other ways to write that last line but if book_id has an index the LIKE filter is probably still helpful.

Upvotes: 6

Related Questions