PostgreSQL: Sub-Query on one table

My table looks (simplified) like this:

ID, File, Subst_ID
1, "AAA"
2, "BBB", 3
3, "CCC"

It records filenames “File”; sometimes, a file (here BBB) is replaced by a newer one (here CCC”. First show all records, that have been replaced:

SELECT ID, File, Subst_ID FROM Table WHERE Subst_ID IS NOT NULL;

OK, brings up line 2. Now I need to add a subquery column QQ, which shows the File of the substitute record, e.g. should bring:

2, "BBB", 3, "CCC"

. My approach does not work:

SELECT ID, File, Subst_ID, (SELECT File FROM Table WHERE Subst-ID = ID) AS QQ FROM Table WHERE Subst_ID IS NOT NULL;

Where is my mistake? Thank you!

Answer

I think you want a self-join:

select t1.*, t2.file as subst_file
from mytable t1
inner join mytable t2 on t2.id = t1.subst_id

This is query you wanted to write – I find that is less neat, because it needs a where clause, while the above does not:

select t1.*,
    (select t2.file from mytable t2 where t2.id = t1.subst_id) as subst_file
from mytable t1
where t1.subst_id is not null