Stephen Lasky Stephen Lasky - 6 months ago 6
SQL Question

SQL: Specify Which Table A Column Name Is Referring To

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 ]


I need to somehow specify that the x on the right hand side is referring to the current value of x from the outer select statement. Currently, it believes that it is referring to itself so it always returns true.

Answer

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

Comments