SCramphorn SCramphorn - 2 months ago 8
SQL Question

Display String based on Int Value in a SELECT JOIN statement

I have a select statement which I want to convert to string depending on it's value i.e.

I want something along the lines of:

SELECT SchoolID (IF SchoolID = 1 Convert STRING("London"), IF SchoolID = 2 Convert STRING ("Glasglow") ELSE Convert STRING(new school")),
L.Location
FROM Schools SC
Join Location L
On L.LocationID = SC.LocationID
Where SchoolID < 200


Not sure how to approach this.

Many Thanks,
Sam

-- EDIT The parenthesis is the Syntax I should be looking at not the actual code

Answer

You can use the CASE expression like this:

SELECT SchoolID
, case SchoolID
    when 1 then 'London'
    when 2 then 'Glasglow'
    else        'new school' 
  end as SchoolName
, L.Location
FROM Schools SC
Join Location L
On L.LocationID = SC.LocationID 
Where SchoolID < 200