How to use column' value in subquery?

Question

I have query like this.

SELECT col1,
       (SELECT another_col1 FROM table1 WHERE table1.date = col1) AS some_col
FROM table1
ORDER BY id;

But I can't use col1 in subquery, it always returns last record but not matching.

How can I use it in PostgreSQL?

Answer 1

You should alias the tables ! Unless the columns you are comparing are unique, the optimizer will take each column on his current level, E.G. - if you are comparing in the correlated query, and the inner table has the same column name as the outer table, the optimizer will think you are meaning to compare the column from the inner table.

SELECT t.col1,
      (SELECT s.another_col1 FROM Table1 s WHERE s.date = t.col1) as some_col
FROM table1 t
ORDER BY t.id