2

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
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Mowgli
  • 3,422
  • 21
  • 64
  • 88
  • 1
    Please provide sample data and an example result set like in [this question](http://stackoverflow.com/questions/12358842/how-to-get-distinct-rows-with-max-value/). You specify SQL Server in the title but MySQL and Oracle in the tags. Can you clarify which product you are using? – Iain Samuel McLean Elder Sep 13 '12 at 18:46
  • why cant you do the same in `select` part of the query? – Phani Rahul Sep 13 '12 at 18:50
  • Why is book_id ever equal to a school_id or a college_id? I see serious data model problems here. Also why is it tagged both SQL Server and Oracle? The syntax for the two platforms will likely be different. – Aaron Bertrand Sep 13 '12 at 18:56
  • Please show sample data and desired results, including what you want to happen when a.book_id is not like AB or CB. – Aaron Bertrand Sep 13 '12 at 18:57
  • if a.book = CBQ111 then Q111 is in FinishedStudent table not CBQ11,so I need to just compare last 3 character. – Mowgli Sep 13 '12 at 18:59
  • Please read: http://dba.blogoverflow.com/2012/06/help-us-help-you/ – Aaron Bertrand Sep 13 '12 at 19:07
  • Thanks, I'll make sure to explain better next time. I tried best to word it out. – Mowgli Sep 13 '12 at 19:10
  • We're much better at reading sample data and desired results than word problems. – Aaron Bertrand Sep 13 '12 at 19:17

2 Answers2

6

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I was writing the same answer, then I asked myself: isn't an `ELSE` required here? What if `a.book_id` doesn't match any of those conditions? – bfavaretto Sep 13 '12 at 18:55
  • @bfavaretto then the row isn't returned. We can only work with the information we've been given. – Aaron Bertrand Sep 13 '12 at 18:56
  • So you mean `CASE` would return `NULL` or something like that in this case? That would make sense. – bfavaretto Sep 13 '12 at 18:58
  • Right, and a.book_id = NULL would return unknown which, for all intents and purposes, is equivalent to false, which means discard the row. – Aaron Bertrand Sep 13 '12 at 18:59
  • @AaronBertrand thanks for fixing syntax, but here is real problem. if `a.book` = CBQ111 then Q111 is in FinishedStudent table not CBQ11,so I need to just compare last 3 character. – Mowgli Sep 13 '12 at 19:00
  • @Mogil edit your question. Show sample data and desired results. Please don't litter answers with comments describing word problems about what you want. – Aaron Bertrand Sep 13 '12 at 19:00
  • I need help fixing 2nd case, when a.book_id start with `CB` then cutoff CB and remaning part of it = b.college_id. how can I code that? – Mowgli Sep 13 '12 at 19:02
  • Yes, that is where, I am trying to get to, but I someone asked for example, I couldn't word it any better. – Mowgli Sep 13 '12 at 19:09
  • Do you mean `then`? instead of `AND`? – Mowgli Sep 13 '12 at 19:25
  • @Mogil I don't understand. Do you mean in the second query? No, I don't mean `then`. There is no `CASE` there. – Aaron Bertrand Sep 13 '12 at 19:28
0

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
wtjones
  • 4,090
  • 4
  • 34
  • 41