SELECT on self-relational table [closed]

Question

SELECT on self-relational table [closed]

Navigation

#1 by (3 votes)

1

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

Each employee has a idchefe that references another employee (who is their boss).

How to make a SELECT that returns the official name and the name of your boss?

mysql select

asked by anonymous 18.12.2018 / 01:01

1 answer

Upload with multiple files only fills the first field What SQL Script in Postgres to return the Functions of a Schema? [closed]

score 3 · Accepted Answer

As the structure of your table funcionario has not been posted, 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 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 question.

Just run this 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)