Not enable to make a where clause after a Left join using subquery

Question

When i try to make a sub-query with a left join i got an error here's an example.

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 will triggered the following error :

Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "TSubQuery.Code" could not be bound.

So i solved this problem by changing my Where into a Inner join clause.

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 ?

there's an other way to solve it without using an inner join or with a with clause ?

why I don't have this problem when am I using a normal table or with a 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

Read this https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Ilyes, Dec 13 '17 at 10:05
What causing the error is `Select * FROM (` cause you have `code` column in both tables — Ilyes, Dec 13 '17 at 10:14
@Sami You are right but since OP looks confused about "old" syntax I posted a answer to explicit the implicit join error (pun intended) =) — jean, Dec 13 '17 at 10:44

score 1 · Accepted Answer · edited Jul 29 '20 at 18:25

1

At first as I say in the comment before, do not use Old-Style joins. What causing the error is that you have the same column name in both table (Code column in your case), and SQL Server will don't know which one you refer too, like you say to me "Esperadoce" and I say which "Esperadoce" you refer to?.

What would be the solution for that?

The solution is using Aliases as:

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

Also you have a Mixed query with Old and modern style joins, So solution would be:

Use Columns names with Aliases instead of SELECT *.
Use Modern style joins instead of the old style, and do not mix them.

edited Jul 29 '20 at 18:25

Community

1
1

answered Dec 13 '17 at 10:21

Ilyes

14,640
4
29
55

But why it's work when i don't use SubQuery or replace my subquery with a 'with clause' – Hicham Bouchilkhi Dec 13 '17 at 10:25
@Esperadoce So your real question is _Why the first Mixed not working, while the second Mixed query work?_ ? – Ilyes Dec 13 '17 at 10:39

jean · Answer 2 · 2017-12-13T10:40:34.573

1

Implicit JOIN is the problem here, see the line below

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

and pay attention to

TSubQuery, Table2

remove that coma and move table2 to a explicit join. You are mixing the old "deprecated" syntax with "modern" explicit join.

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing

edited Dec 13 '17 at 10:40

answered Dec 13 '17 at 10:26

jean

4,159
4
31
52

score 1 · Answer 3 · answered Dec 13 '17 at 10:36

Don't use the where clause for join conditions. Do this, working fiddle

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]

The legacy join syntax is obsolete and provided only for backward compatibility. And whats more, it sucks.

Additionally, for what its worth, don't use SELECT *.

score 0 · Answer 4 · answered Dec 13 '17 at 10:04

0

Try this

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

answered Dec 13 '17 at 10:04

Jayasurya Satheesh

7,826
3
22
39

over kill, CTE not required – Jodrell Dec 13 '17 at 10:41
you can also use subquery instead – Jayasurya Satheesh Dec 13 '17 at 10:41

score 0 · Answer 5 · answered Dec 13 '17 at 10:26

0

the subquery can be avoided in this way:

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

answered Dec 13 '17 at 10:26

Jayvee

10,670
3
29
40

Not enable to make a where clause after a Left join using subquery

5 Answers5