CODEWITHSUNDEEP
sqlsql-server
Stephen Lasky
Stephen Lasky

Reputation: 447

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.

Upvotes: 2

Views: 116

Answers (3)

EoinS
EoinS

Reputation: 5482

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

Can we with or without 'AS' in Sql server:

table_name AS table alias
table_name table_alias

More info

Upvotes: 5

Jorge Campos
Jorge Campos

Reputation: 23381

Just use:

 SELECT DISTINCT yOut.x,
 (SELECT yIN.x FROM Y as yIN WHERE yIN.x=yOut.x) as someColName
 FROM Y as yOut                      ^    ^
                                     |    |
                                     |    |_ [x referring to outer select statement]
[is your table alias ] ______________| 
[from inner select]

Upvotes: 2

Brian Cross
Brian Cross

Reputation: 162

I would suggest to use aliases for your tables to make it very clear.

Upvotes: 0

Related Questions