CODEWITHSUNDEEP
phpmysqlsqljoin
Oscar Ho
Oscar Ho

Reputation: 15

How to show all result and null with joining two table?

I have two table structures here:

employee 
(emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100),
emp_ic VARCHAR(20),
emp_phone VARCHAR(20),
emp_address VARCHAR(200))

employee_medical
(id INT AUTO_INCREMENT,
 emp_id INT,
 medical_notes TEXT
 PRIMARY KEY (id),
 FOREIGN KEY (emp_id) REFERENCES employee(emp_id))

I want to join two table and show all employee result and only medical_notes on employee_medical table

But when I type this it didn't work:

SELECT employee.emp_id, employee.emp_name , employee.emp_ic,employee.emp_phone, employee.emp_address , employee_medical.medical_notes
FROM employee 
JOIN employee_medical 
WHERE employee.emp_id = employee_medical.emp_id

This only show like it :

 emp_id  emp_name   emp_ic  emp_phone  emp_address   medical_notes    
   1        a        1          1             1            2        
   2        b        1          1             1            2

But I want to get results like this:

 emp_id  emp_name   emp_ic  emp_phone  emp_address   medical_notes    
   1        a        1          1             1            2        
   2        b        1          1             1            2 
   3        c        1          1             1            null
   4        d        1          1             1            null
   5        e        1          1             1            null
   6        f        1          1             1            null

How can I show all result like it? Please help.

Upvotes: 0

Views: 50

Answers (1)

Arun Krish
Arun Krish

Reputation: 2153

Try this

SELECT employee.emp_id, employee.emp_name,  employee.emp_ic,employee.emp_phone, employee.emp_address , if(employee_medical.medical_notes = '', NULL, employee_medical.medical_notes) AS medical_notes
FROM employee 
LEFT JOIN employee_medical ON(employee_medical.emp_id =employee.emp_id)

Upvotes: 1

Related Questions