'display all departments and all employees

if there :

(department) table: (id,name)

(employee) table : (id,dept_id,name)

how to show every department (id,name), then all employees (id,name) in this department under its department.

I'd like it as SQL statment

sql


Solution 1:[1]

You need to use JOIN

I believe it's something like this:

SELECT department.id, department.name, employee.id, employee.name
FROM department
LEFT JOIN employee
ON department.id=employee.dept_id
ORDER BY department.id

Solution 2:[2]

Since all employees must be present under a particular department at any time, you can do a inner join on both the table with dept_id like

SELECT dept.id, dept.name, emp.id, emp.name
FROM department dept
JOIN employee emp
ON dept.id=emp.dept_id

Solution 3:[3]

Simply try this

SELECT D.ID,D.Name,E.ID,E.Name
FROM Department D Left JOIN Employee E ON E.dept_id = D.Id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rahul
Solution 2 Rahul
Solution 3 Vignesh Kumar A

Related Questions