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

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?

CodePudding user response：

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