CODEWITHSUNDEEP
sqlsql-server
Daniel Lizik
Daniel Lizik

Reputation: 3144

Use a casted column name in a join query in sql-server

Say I have a query like that casts an xml query

SELECT TOP 5
  CAST (row.query('col(/xml/tag)'), as NVARCHAR(100)) AS 'foobar'
FROM db.table

How can I use the casted result of the xml query in a LEFT JOIN?

SELECT TOP 5
  CAST (row.query('col(/xml/tag)'), as NVARCHAR(100)) AS 'foobar'
FROM db.table
LEFT JOIN db.table_table
-- don't know the syntax for this part
ON db.table['foobar'] = db.table_table.col

Upvotes: 1

Views: 104

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

Make the original query as sub-select

SELECT foobar
FROM   (SELECT TOP 5 Cast (row.query('col(/xml/tag)') AS NVARCHAR(100)) AS foobar
        FROM   db.TABLE) t
       LEFT JOIN db.table_table
              ON t.foobar = db.table_table.col

Upvotes: 2

Related Questions