CODEWITHSUNDEEP
mysqljoin
Jordashiro
Jordashiro

Reputation: 805

Joining tables using foreign key

I have 2 tables , employees and departments.

departments(id, department)

employees(id, department_id, name, and a bunch more here)

so employees.department_id is a foreign key to departments.id.

I need to show the table employees, but instead of department_id (showing the IDs of the departments) I need to show the actual departments name, so in place of department_id, i need to place departments.department.

How should I do this?

Upvotes: 14

Views: 57693

Answers (5)

Ryan Kempt
Ryan Kempt

Reputation: 4209

SELECT employees.id, employees.department_id, employees.name, departments.department
FROM employees
INNER JOIN departments ON employees.department_id = departments.id

Upvotes: 8

Anthony
Anthony

Reputation: 236

Your friend told you the truth :p

You just have to use a inner join between your two tables like this:

SELECT d.name, e.name, e.email, ... FROM deparments d INNER JOIN employees e ON d.id = e.department_id.

You have to adapt your field to have the desired output :)

Upvotes: 21

St.Woland
St.Woland

Reputation: 5417

SELECT employees.*, department.department
FROM employees
INNER JOIN department ON employees.department_id = department.id

Upvotes: 0

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

This should cover it for you:

SELECT
    E.Id
    , D.Department
    , E.Name
    -- More stuff
FROM Employees E
INNER JOIN Departments D
    ON D.id = E.department_id

Upvotes: 0

Michael Laffargue
Michael Laffargue

Reputation: 10294

You should not use SELECT * and just take the fields you really want if it's only to take a screenshot of the table values.

Like SELECT department.name

Upvotes: 1

Related Questions