Mysql Query – Set 1

We have Employee,Department,Region tables as shown below:

Employee(EmpID, EmpName, Salary)

Department(DeptID, EmpID, DeptName)

Region(RegnID, DeptID, RegnName )

Now get all employee details according to the region (RegnName) they belong?

Solution:

If you want to have all of the employees in the results, despite missing department and region data then you need to use OUTER joins:


Select r.RegnName as 'Region', d.DeptName as 'Dept', e.EmpID as 'Id', e.EmpName as 'Name', e.Salary as Salary
FROM Employee LEFT OUTER JOIN Department d ON e.EmpID = d.EmpID
LEFT OUTER JOIN Region r ON d.DeptID = r.DeptID
ORDER BY r.RegnName, d.DeptName

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation