I have two tables:
CREATE TABLE roles (
id INT PRIMARY KEY,
title VARCHAR(30)
);
CREATE TABLE employee (
id INT PRIMARY KEY,
first_name VARCHAR(30),
role_id INT
);
Where role_id corresponds to a role with a matching ID in the "roles" table. I am wanting to select my database in such a way that the end result would look like:
---- ------------ -------------------
| id | first_name | role |
---- ------------ -------------------
| 1 | Jane | Manager |
| 2 | Patrick | Project lead |
| 3 | Robert | Computer Engineer |
---- ------------ -------------------
So that the user can view the employee data without the added clutter of employee.role_id and roles.id. I know I can use:
SELECT * FROM employee
RIGHT JOIN roles
ON role_id = roles.id;
To show all employees along with their corresponding roles, but in using this the roles.id and employee.role_id numbers are displayed along with the rest of the table, which is less than ideal for my case. I have toyed around with the idea of inserting the results into a third table, but I would rather not do this for simplicity's sake if possible.
How can I accomplish this?
I'm very new to MySQL and database management, and Googling around for the past day or two has revealed little in the way of a solution. (Or, more likely, a solution I was able to recognize as being the solution to my problem) I am using MySQL Server version 8.0.
CodePudding user response：
If my understanding of your requirement is correct, you want select only some columns:
SELECT employee.id as id, first_name, title as role
FROM employee
RIGHT JOIN roles
ON role_id = roles.id;
CodePudding user response：
This is a basic select join
select e.id,
e.first_name,
r.title
from roles r
inner join employee e on e.role_id=r.id;
Note. If there are employee without role change inner join to left join