CODEWITHSUNDEEP
t-sql
JeffreyPMc
JeffreyPMc

Reputation: 3

What is the proper syntax to alias a join that contains a subquery?

I am trying to alias a JOIN that has a subquery.

RIGHT OUTER JOIN
    (
     SELECT   FieldA, FieldB
     FROM Table1
     JOIN Table2
     ON Table1.WellId = Table2.WellId
    ) AS wi
        AS MProd

ON Units.RS_Unit_Name = RTRIM(wi.RS_POOL) + '-' + wi.RS_Field

I get incorrect syntax near 'AS'...

wi is the alias FieldA & FieldB; How do I alias the RIGHT OUTER JOIN?

My original code was:

RIGHT OUTER JOIN TABLE1
        AS MProd

ON Units.RS_Unit_Name = RTRIM(wi.RS_POOL) + '-' + wi.RS_Field

which worked fine. I replaced TABLE1 with a subquery which I did in the code sample. I just can't seem to figure out the proper syntax to set MProd as the alias.

Upvotes: 0

Views: 28

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

The problem is this line of code:

 AS wi AS MProd

This is not valid syntax. It can be AS wi or AS MProd, but not AS wi AS MProd.

This is valid:

RIGHT OUTER JOIN 
( 
  SELECT FieldA, FieldB 
  FROM   Table1 
  JOIN   Table2 ON Table1.WellId = Table2.WellId 
) AS MProd
ON Units.RS_Unit_Name = RTRIM(MProd.RS_POOL) + '-' + MProd.RS_Field ...

and so is this:

RIGHT OUTER JOIN 
( 
  SELECT FieldA, FieldB 
  FROM   Table1 
  JOIN   Table2 ON Table1.WellId = Table2.WellId 
) AS wi
ON Units.RS_Unit_Name = RTRIM(wi.RS_POOL) + '-' + wi.RS_Field ...

Upvotes: 2

Related Questions