CODEWITHSUNDEEP
sql
FabriGad Ahmed
FabriGad Ahmed

Reputation: 166

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

Upvotes: 0

Views: 2731

Answers (3)

fredrik.hjarner
fredrik.hjarner

Reputation: 710

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

Upvotes: 1

Rahul
Rahul

Reputation: 77866

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

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

Upvotes: 0

Related Questions