CODEWITHSUNDEEP
sqlsql-server
generic_user
generic_user

Reputation: 3562

Can you join on a variable name that you define using `as` (in SQL)

This works:

select
    x.long_name as ln
from x
join y on y.ln = x.long_name

This doesn't:

select
    x.long_name as ln
from x
join y on y.ln = x.ln

can it be made to work somehow? In a way that is simple and compact? It'd be convenient because I'm generating filter tables (y would be a filter table) and queries programmatically.

Upvotes: 0

Views: 862

Answers (2)

The Impaler
The Impaler

Reputation: 48850

You'll need to use a "table expression" to use a column from a "result set". 

select *
from ( -- here we create a table expression named "z"
  select
    long_name as ln
  from x
) z
join y on y.ln = z.ln

As you see the table expression "z" has the column you want.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270341

You can use apply to define the alias in the from clause:

select v.ln
from x cross apply
     (values (x.long_name)) v(ln) join
     y
     on y.ln = v.ln

Other alternatives are subqueries and CTEs.

Upvotes: 3

Related Questions