1

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.

Shades
  • 667
  • 1
  • 7
  • 22
  • Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. – outis Sep 10 '22 at 21:17
  • See the [help] for more on [how to ask good questions](/help/how-to-ask) and many other helpful topics. – outis Sep 10 '22 at 21:17
  • Does this answer your question? [Exclude a column using SELECT \* \[except columnA\] FROM tableA?](/q/729197/90527) – outis Sep 10 '22 at 21:20
  • See also: "[Why is SELECT * considered harmful?](/q/3639861/90527)" – outis Sep 10 '22 at 21:27
  • @outis Thank you for your feedback, being new to MySQL I'm also new to asking questions about it! I'll try and edit my post to include the DDL statements rather than just tables. In terms of the other post you suggested, I actually read that before posting this question. It wasn't helpful with my problem, unfortunately, since the selected answer involves creating a second table, the second answer is just a simple no, and the other's aren't much more helpful. The answers given here were far more elegant and useful for solving my problem. – Shades Sep 10 '22 at 21:30

2 Answers2

1

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;
Jens
  • 67,715
  • 15
  • 98
  • 113
1

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

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28