CODEWITHSUNDEEP
mysqlsqljoincase
knowlede0989
knowlede0989

Reputation: 43

Case statement and join

The question is

JOIN DEPT_EMP AND DEPARTMENTS AND EMPLOYEES and display the Employee full name and if Gender of employee is a M then display it as Male and if F then Female and name it Gender_Column.

This is what I came up with so far:

SELECT CONCAT(e.FIRST_NAME,' ', e.LAST_NAME) AS Full_name, e.Gender
From dept_emp de
JOIN departments d
ON de.dept_no = d.dept_no
JOIN employees e
ON de.emp_no = e.emp_no
        CASE
        WHEN E.gender = 'm' Then 'male'
        WHEN E.gender = 'f' Then 'female'
END

However I get the error message

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'CASE'

Upvotes: 0

Views: 1055

Answers (2)

Adrian Ang
Adrian Ang

Reputation: 580

When you need to do conditional column join, then specify CASE WHEN after JOIN.

The CASE WHEN condition to filter columns should be right after the SELECT column with a comma, for example

SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'heavy-weight'
            ELSE 'light-weight' 
       END AS weight_group
  FROM football_players

So in your question, the solution should be

SELECT CONCAT(e.FIRST_NAME,' ', e.LAST_NAME) AS Full_name, 
       e.Gender,
         CASE
           WHEN e.gender = 'm' THEN 'male'
           WHEN e.gender = 'f' THEN 'female'
         END AS Gender_Column
FROM dept_emp de
JOIN departments d
     ON de.dept_no = d.dept_no
JOIN employees e
     ON de.emp_no = e.emp_no

Upvotes: 2

LoztInSpace
LoztInSpace

Reputation: 5697

You need to put the case into the select, not the join.

  SELECT CONCAT(e.FIRST_NAME,' ', e.LAST_NAME) AS Full_name,e.Gender , 
CASE WHEN E.gender = 'm' Then 'male' WHEN E.gender = 'f' Then 'female' END as Gender_Text
     From dept_emp de JOIN departments d ON de.dept_no = d.dept_no JOIN employees e ON de.emp_no = e.emp_no

Also no column name alias but that's not an error.

Upvotes: 1

Related Questions