Write a query to print the employee name and UIN in each line. If no UIN is present print NULL in its place. The order of output does not matter

user_11686131 0
Viewed 24726

There are two tables EMPLOYEES and EMPLOYEE_PAN. We have to print employee NAME from EMPLOYEES table and UIN from EMPLOYEE_PAN table. ID is in both the tables used as a primary key. If no UIN is present in EMPLOYEE_PAN table then we have to print NULL instead of UIN. UIN is an integer.

I have tried this code. I am facing an error in the case when statement part. I am facing a problem in case statements when JOIN is used in the query. Please let me know how to use CASE statement with JOIN.

SELECT UIN, NAME 
FROM EMPLOYEE_PAN JOIN EMPLOYEES 
ON EMPLOYEES.ID = EMPLOYEE_PAN.ID 
CASE WHEN EMPLOYEE_PAN.UIN != 0 THEN EMPLOYEE_PAN.UIN END ;
mysql
2 Answers

You need left join 

SELECT EMPLOYEE_PAN.UIN, EMPLOYEES.NAME 
FROM EMPLOYEES    
LEFT JOIN EMPLOYEE_PAN  ON EMPLOYEES.ID = EMPLOYEE_PAN.ID

left join return null value for not matching keys

user_35223120

Use a case statement in SELECT clause

SELECT 
    CASE WHEN EMPLOYEE_PAN.UIN != 0 THEN EMPLOYEE_PAN.UIN ELSE "NULL" END AS UIN, NAME 
FROM 
    EMPLOYEE_PAN JOIN EMPLOYEES 
    ON EMPLOYEES.ID = EMPLOYEE_PAN.ID;

Note: I consider no UIN present in the table means UIN value is 0 (from your query )

user_82395000
Related