SELECT in auto-relational table

Asked

Viewed 72 times

1

I have a table funcionario, with attributes: ID(PK) and IDCHEFE (FK from the table itself).

Each employee(a) has one idchefe which refers to another official (who is his boss(a)).

How to make a SELECT who returns the employee’s name and the name of his boss?

mysql database select
  • 1

    Always post your code/structure related to the question. It makes it easier for those who help you! Help Center > Asking

1 answer

3


As the structure of your table has not been posted funcionario, I will suggest the following:

CREATE TABLE `funcionario`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `nome` varchar(255) NOT NULL,
    `chefeId` int(11) NULL DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;

I’ll add some records to test the query:

INSERT INTO `funcionario` (`nome`, `chefeId`) VALUES
    ('Joaquim', 4),
    ('João', 1),
    ('José', 1),
    ('Jurandir', 5),
    ('Josefina', 2);

Now I can help you with your doubt.

Just spin that one query:

SELECT
    `fn`.`nome` AS `funcionario`,
    `cf`.`nome` AS `chefe`
FROM `funcionario` AS `fn`
INNER JOIN `funcionario` AS `cf`
ON `fn`.`chefeId` = `cf`.`id`;

The result is this:

+-------------+----------+
| funcionario | chefe    |
+-------------+----------+
| João        | Joaquim  |
| José        | Joaquim  |
| Josefina    | João     |
| Joaquim     | Jurandir |
| Jurandir    | Josefina |
+-------------+----------+
5 rows in set (0.05 sec)

Browser other questions tagged mysql database select

You are not signed in. Login or sign up in order to post.