Do not allow the creation of a where clause after a left join using a subquery

Question

When I try to make a subquery with left join, I got an error here.

Select * FROM ( SELECT Code,Column2 FROM T1) TSubQuery, Table2 T2
Left join Table3 T3
On T3.Code = TSubQuery.Code  --  <== Error triggered
Where TSubQuery.Code= T2.Code

this code causes the following error:

Msg 4104, Level 16, State 1, Line 3 Multiple identifier "TSubQuery.Code" cannot be bound.

So, I solved this problem by changing mine Whereto a suggestion Inner join.

Select * FROM ( SELECT Code,Column2 FROM T1) TSubQuery
Left join Table3 T3
On T3.Code = TSubQuery.Code 
Inner join  Table2 T2
On T2.Code = TSubQuery.Code 

I would like to understand why I have this error?

is there any other way to solve it without using Inner joinor with with clause?

Why don't I have this problem when I use a regular table or with with table?

Example:

With TQuery as ( SELECT * FROM T1) 
select * from TQuery, Table2 T2
Left join Table3 T3
On T3.Code = TQuery.Code 
Where TQuery.Code = T2.Code

Answer 1

, , . , , ( "" ), SQL Server , , , "Esperadoce", , Esperadoce " ?.

?

Aliases :

SELECT T1.Code AS T1Code, T2.Code AS T2Code From ...

Mixed query . , :

• Aliases SELECT *.

  • " " .

Answer 2

JOIN , .

 FROM ( SELECT Code,Column2 FROM T1) TSubQuery, Table2 T2

TSubQuery, Table2

table2 . "" "" .

" " JOIN :

Answer 3

where . ,

SELECT
      *
  FROM
      (
        SELECT
              [Code],
              [Column2]
          FROM
              [dbo].[T1]
      ) TSubQuery
    JOIN
      [dbo].[Table2] T2
        ON TSubQuery.[Code] = T2.[Code]
    LEFT JOIN
      [dbo].[Table3] T3
        ON T3.[Code] = TSubQuery.[Code]

. , .

, SELECT *.

Answer 4

with TSubQuery
as
(
    SELECT Code,Column2 FROM T1
)
Select 
    * 
    FROM TSubQuery
        left join Table2 T2
            on TSubQuery.Code= T2.Code
        Left join Table3 T3
            On T3.Code = TSubQuery.Code

Answer 5

:

SELECT Code,Column2 FROM T1 as TSubQuery
Left join Table3 T3
On T3.Code = TSubQuery.Code 
Inner join  Table2 T2
On T2.Code = TSubQuery.Code