I am selecting data from the same table in one SQL query and need to specify which table the particular column I am using refers to.
BASICALLY: x is ambiguous and I need to make it non-ambiguous.
For example I have...
SELECT DISTINCT x,
(SELECT x FROM Y WHERE x=x)
FROM Y ^ ^
| |_ [x referring to outer select statement]
[x referring to inner] _|
[ select statement ]
you should use table aliases:
SELECT DISTINCT h.x, (SELECT x FROM Y as g WHERE g.x=h.x) FROM Y as h
This way you can create temp table names and point to them, especially helpful for self-joins