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