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!
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