Inner join query don't retrieve the most recent result

Question

I need to update my Item field "Vendor No." according to the most recent result of the field "Source No." of table "Item ledger Entry"

I tried to do a inner join between both tables (item and item ledger entry) but some of the results of this query was not the most recent.

So If i do this query with a specific case,

select top(1)
    [MR$Item].[No_], 
    [MR$Item Ledger Entry].[Item No_],
    [MR$Item].[Vendor No_], 
    [MR$Item Ledger Entry].[Source No_], 
    [MR$Item Ledger Entry].[Entry Type],
    [MR$Item Ledger Entry].[Posting Date]
from [MR$Item]
    left outer join [MR$Item Ledger Entry] on [MR$Item].[No_] = [MR$Item Ledger Entry].[Item No_] 
where
    [MR$Item Ledger Entry].[Entry Type] = 0
    and [MR$Item].[Vendor No_] <> [MR$Item Ledger Entry].[Source No_]
    AND year([MR$Item Ledger Entry].[Posting Date])>=2018
    AND [MR$Item].[No_] = '3510100011' 
order by [MR$Item Ledger Entry].[Posting Date] DESC

I get this results

No_         Item No_    Vendor No_  Source No_  Entry Type  Posting Date
3510100011  3510100011  505881497   172140064   0           2018-09-27 00:00:00.000

But doing this simple select, I can see that the last result is not right

   select top(1)
        [MR$Item Ledger Entry].[Item No_],
        [MR$Item Ledger Entry].[Source No_], 
        [MR$Item Ledger Entry].[Entry Type],
        [MR$Item Ledger Entry].[Posting Date]
    from [MR$Item Ledger Entry]
    where
        [MR$Item Ledger Entry].[Entry Type] = 0 AND 
        year([MR$Item Ledger Entry].[Posting Date]) >= 2018 AND
        [MR$Item Ledger Entry].[Item No_] = '3510100011' 
order by [MR$Item Ledger Entry].[Posting Date] DESC

The results are

Item No_    Source No_  Entry Type  Posting Date
3510100011  508606977   0   2018-01-09 00:00:00.000

And the correct result is for the item "3510100011" I should have the number "508606977".

What am I doing wrong?

Presumably the other conditions are filtering out the row you want. — Gordon Linoff, Oct 04 '18 at 13:49
Do you want to latest `Source No_` for all `Item No_` or just the one in your example? — Jamie Lester, Oct 04 '18 at 13:51
@JamieLester I want all records, this is a example that my query is not getting the most recent — Cátia Matos, Oct 04 '18 at 14:03
The problem was here "and [MR$Item].[Vendor No_] <> [MR$Item Ledger Entry].[Source No_]".. In all cases that they are the same, the most recent result is ignored. So now I try to do my query with select distinct but it is not filtering the duplicates — Cátia Matos, Oct 04 '18 at 14:29

score -1 · Answer 1 · answered Oct 04 '18 at 13:41

-1

Because in your query, you put :

AND [MR$Item Ledger Entry].[Item No_]='3510100011'

And, you can change your left join to inner join, because you are doing where on the table [MR$Item Ledger Entry]

answered Oct 04 '18 at 13:41

DanB

2,022
1
12
24

Inner join query don't retrieve the most recent result

1 Answers1