Search records referencing the same table

Asked

Viewed 696 times

1

I own a table that she owns an fk for herself. The point of this is to assemble a history of the previous records, so I have the id and id_previous column, and the previous id_fk for the id column. The problem is that I can’t get more than one record, only 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

  • "only an Row" you receive is in the direct query in the bank or in the code? if it is in the code the problem may not be in the select (that in my view is correct)

  • The bank search actually I have the Where condition to specify which accurate record and its previous one. When I inform Where it will bring only 1

  • Can’t that be your condition that limits the return to only one?

  • Yes, but how do I condition the search to bring the record history I need?

  • From what I understand if you consult for p1.id = 3 it will bring all the records that exist and have 3 as id_anterior. I don’t know if it can happen in your system of more than one line to be earlier than 3. If yes, I think you will have to give more information about the system; if not, the select that is correct.

1 answer

2


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 );

Dice:

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:

https://coderwall.com/p/whf3-a/hierarchical-data-in-postgres

https://www.postgresql.org/docs/9.6/static/queries-with.html

  • I did using your reference. Thanks for the idea, but I will leave in a single table at the moment.

Browser other questions tagged postgresql

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