Searches for records that reference the same table

Question

Searches for records that reference the same table

Navigation

#1 by (2 votes)

1

I have a table that it has a fk for itself. The sense of this is to set up a history of the previous records, so I have the column id and old_id, the old_id is fk for the id column. The problem is that I can not get more than one record, just one row.

Example:

table_historico

id |  id_anterior
1  |  NULL
2  |  1
3  |  2

My attempts were:

SELECT p1.id, p1.id_anterior, p2.id, p2.id_anterior
FROM table_historico p1
LEFT JOIN table_historico p2 ON p1.id_anterior = p2.id
WHERE p1.id = 3

postgresql

asked by anonymous 22.08.2017 / 19:31

1 answer

How do I remove listeners according to the state of the component? Passing parameter through URL

score 2 · Accepted Answer

You need a recursive query to solve your problem.

Structure:

CREATE TABLE tbl_historico
(
    id INTEGER,
    id_anterior INTEGER
);

ALTER TABLE tbl_historico ADD PRIMARY KEY (id);
ALTER TABLE tbl_historico ADD CONSTRAINT fk_historico FOREIGN KEY(id_anterior) REFERENCES tbl_historico( id );

Data:

INSERT INTO tbl_historico ( id, id_anterior ) VALUES ( 1, NULL );
INSERT INTO tbl_historico ( id, id_anterior ) VALUES ( 2, 1 );
INSERT INTO tbl_historico ( id, id_anterior ) VALUES ( 3, 2 );

Solution:

WITH RECURSIVE item_historico( id, id_anterior ) AS
(
    SELECT a.id, a.id_anterior FROM tbl_historico a WHERE a.id = 3
    UNION
    SELECT b.id, b.id_anterior FROM tbl_historico b, item_historico a WHERE a.id_anterior = b.id
)
SELECT * FROM item_historico;

References:

link