Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Inserting a title when two IDs match, but excluding IDs from the query

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;

    https://dbfiddle.uk/ba-Hh-8P

    Note. If there are employee without role change inner join to left join

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search