INNER JOIN not working for the first table in a multi table select statement

Question

I am trying to select data from same table twice and it works but it is not allowing me to use the first table in the list in INNER JOIN, if I use the second table from the list in the join it works

So this is the query that works fine

SELECT 
    TOP 1 
    RIT_First.LastModified, 
    RIT_Last.LastModified
FROM 
    dbo.ItemTransaction RIT_First, 
    dbo.ItemTransaction RIT_Last
JOIN dbo.Item RI ON RIT_Last.ItemId = RI.Id -- Using the second table in the join

But when I do this

SELECT 
    TOP 1 
    RIT_First.LastModified, 
    RIT_Last.LastModified
FROM 
    dbo.ItemTransaction RIT_First, 
    dbo.ItemTransaction RIT_Last
JOIN dbo.Item RI ON RIT_First.ItemId = RI.Id -- Using the first table in the join

I start getting this error

The multi-part identifier "RIT_First.ItemId" could not be bound.

So is it like we can use only last table in the list of tables in the join or I am doing something wrong?

Why are you mix and matching ANSI-92 and ANSI-89 JOINs here? — Thom A, Sep 09 '22 at 08:58
Checkout: https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 — Helio, Sep 09 '22 at 09:03

Thom A · Accepted Answer · 2022-09-09T09:11:55.713

The problem here is your mix and matching of ANSI-89 JOINs and ANSI-92 JOINs. Just don't use ANSI-89 JOINs any more; they were superseded 30 years ago.

If you use ANSI-92 JOINs the whole way through, you don't have a problem:

SELECT TOP (1)
       RIT_First.LastModified,
       RIT_Last.LastModified
FROM dbo.ItemTransaction RIT_First
     CROSS JOIN dbo.ItemTransaction RIT_Last
     JOIN dbo.Item RI ON RIT_First.ItemId = RI.Id
ORDER BY {Expression(s)};

If, for some bizarre reason, you are compelled to use an ANSI-89 JOIN, then you would need to put the join criteria in the WHERE:

SELECT TOP (1)
       RIT_First.LastModified,
       RIT_Last.LastModified
FROM dbo.ItemTransaction RIT_First,
     dbo.ItemTransaction RIT_Last,
     dbo.Item RI 
WHERE RIT_First.ItemId = RI.Id
ORDER BY {Expression(s)};

But, like I said, just don't do that. See this article by Aaron Bertrand for more information: Bad Habits to Kick : Using old-style JOINs

INNER JOIN not working for the first table in a multi table select statement

1 Answers1