How To Get Highest Education Using MySQL?

Question

I have problem when try get highest education result. For example I have 5 tables.

Staff

+-----------+----------+
| STAFF ID  | NAME     |
+-----------+----------+
|   001     | Ramesh   |
|   002     | Khilan   |
|   003     | Kaushik  |
|   004     | Chaitali |
|   005     | Hardik   |
|   006     | Komal    |
|   007     | Muffy    |
+-----------+----------+

Diploma

+-----------+-------------------------+
| STAFF ID  |          DIPLOMA        |
+-----------+-------------------------+
|   001     | Diploma in IT           |
|   003     | Diploma in Multimedia   |
|   004     | Diploma in Multimedia   |
|   005     | Diploma in IT           |
+-----------+-------------------------+

Degree

+-----------+-------------------------+
| STAFF ID  |          DEGREE         |
+-----------+-------------------------+
|   002     |  Degree in Science      |
|   003     |  Degree in Multimedia   |
+-----------+-------------------------+

Master

+-----------+-------------------------+
| STAFF ID  |          MASTER         |
+-----------+-------------------------+
|   006     |  Master in Arts         |
|   007     |  Master in Business     |
+-----------+-------------------------+

How to filter staff by highest education the have in MySQL? For example I want to search staff that have only Diploma and didn't want staff name Kaushik that haves both Diploma and Degree to be display in my search result?

Answer 1

You could use the COALESCE function to return the first non null value and then pass in your master,degree,diploma in that order so that it returns the first non-null which is the highest level of education like this

SELECT s.staff_id,
       s.name,
       COALESCE(m.master,d.degree,di.diploma) AS highest_education
FROM staff s
LEFT JOIN master m on s.staff_id = m.staff_id
LEFT JOIN degree d on s.staff_id = d.staff_id
LEFT JOIN diploma di on s.staff_id = di.staff_id

and then to filter out just for diplomas you could add the HAVING highest_education LIKE "Diploma%" like this

SELECT s.staff_id,
       s.name,
       COALESCE(m.master,d.degree,di.diploma) AS highest_education
FROM staff s
LEFT JOIN master m on s.staff_id = m.staff_id
LEFT JOIN degree d on s.staff_id = d.staff_id
LEFT JOIN diploma di on s.staff_id = di.staff_id
HAVING highest_education LIKE "Diploma%"

http://sqlfiddle.com/#!9/e1e47/3

Answer 2

As simple as this.. But I suggest, that you take some time learning how to perform basic sql query.. There are a lot of useful resources that could help you get started.

SELECT * FROM Staff 
WHERE `STAFF ID` IN (SELECT `STAFF ID` FROM Diploma) 
AND `STAFF ID` NOT IN (SELECT `STAFF ID` FROM Degree)

Answer 3

Yet another possibility:

select staff.*,diploma education
  from staff
       left join diploma using(`staff id`)
       left join degree using(`staff id`)
       left join master using(`staff id`)
  where diploma is not null
    and degree is null
    and master is null;