LEFT JOIN error

(0) Share

Report

Posted on by Community Member

Hi I am trying to join three tables to one main table by using a left join

use [SpierLive]
go 

select 
/*Item Ledger Entry Table*/
a.[Entry No_], max(a.[Source No_]), max(a.[Global Dimension 1 Code]) as [Brand Class],
max(a.[Country_Region Code]) as [Country Code], sum(cast(a.[Quantity] as numeric)) [Quantity], 
sum(cast(a.[Remaining Quantity] as numeric)) [Remaining Quantity], max(a.[Variant Code]) as [Variant Code], 

/*Value Entry Table*/
--(b.[Posting Date]), max(b.[Inventory Posting Group]), max(b.[Global Dimension 1 Code] as [Brand Class Code]),
sum(cast(b.[Valued Quantity] as numeric)) [Valued Quantity], sum(cast(b.[Sales Amount (Actual)] as numeric)) [Sales Amount (Actual)], 
sum(cast(b.[Cost Amount (Actual)] as numeric)) [Cost Amount (Actual)], sum(cast(b.[Expected Cost] as numeric)) [Expected Cost], 
sum(cast(b.[Sales Amount (Expected)] as numeric))[Sales Amount (Expected)], 
sum(cast(b.[Cost Amount (Expected)] as numeric)) [Cost Amount (Expected)],

/*Item Table*/
max(c.[Inventory Posting Group]) as [Inventory Posting Group], 
max(c.[Item Category Code]) as [Item Category Code], 
max(c.[Base Unit of Measure]) as [Base Unit of Measure], sum(cast(c.[Unit Cost] as numeric)) [Unit Cost], 
sum(cast(c.[Standard Cost] as numeric)) [Standard Cost], sum(cast(c.[Last Direct Cost] as numeric)) [Last Direct Cost],

/*WineMS Inv_Transaction tbl Table*/
 max(d.[Company]) as [Company], sum(cast(d.[Quantity] as numeric)) [Quantity], sum(cast([Cost Amount] as numeric)) [WineMS Cost Amount],
 max(d.[Brand Group]) as [Brand Group], max(d.[Owner]) as [Owner]
 from 
	 [dbo].[Spier Live$Item Ledger Entry] a, 
	 [dbo].[Spier Live$Value Entry] b, 
	 [dbo].[Spier Live$Item] c,
	 [dbo].[WineMS Inv_Transaction Tbl] d

left join [dbo].[Spier Live$Item Ledger Entry]  on  a.[Item No_] = c.[No_]
left join [dbo].[Spier Live$Value Entry] on b.[Item No_] =c.[No_]
left join [dbo].[WineMS Inv_Transaction Tbl] on d.[Item No] = c.[No_]

 group by a.[Entry No_]

I am getting this error when I execute the script

Msg 4104, Level 16, State 1, Line 32
The multi-part identifier "a.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 32
The multi-part identifier "c.No_" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "b.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "c.No_" could not be bound.
Msg 4104, Level 16, State 1, Line 34
The multi-part identifier "c.No_" could not be bound.
Msg 209, Level 16, State 1, Line 24

if I use a where clause the query runs to long , even though I have a group by entry no, what am I doing wrong.

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

Suresh Kulla 50,241 Super User 2025 Season 2 on at

Like (0)

Report

You query after From [dbo.[Spier Live$Item Ledger Entry] a part is wrong it should be something like

select * from table a left join table b on a. x = b.y left join table c on b.y = c.z something like this you cannot have tables a, table b, table c then joins

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
I have done as you requested but I still get errors , this is my update code

use [SpierLive] go select /*Item Ledger Entry Table*/ a.[Entry No_] , max(a.[Source No_]), max(a.[Global Dimension 1 Code]) as [Brand Class], max(a.[Country_Region Code]) as [Country Code], sum(cast(a.[Quantity] as numeric)) [Quantity], sum(cast(a.[Remaining Quantity] as numeric)) [Remaining Quantity], max(a.[Variant Code]) as [Variant Code], /*Value Entry Table*/ --max(b.[Posting Date]), max(b.[Inventory Posting Group]), max(b.[Global Dimension 1 Code] as [Brand Class Code]), sum(cast(b.[Valued Quantity] as numeric)) [Valued Quantity], sum(cast(b.[Sales Amount (Actual)] as numeric)) [Sales Amount (Actual)], sum(cast(b.[Cost Amount (Actual)] as numeric)) [Cost Amount (Actual)], sum(cast(b.[Expected Cost] as numeric)) [Expected Cost], sum(cast(b.[Sales Amount (Expected)] as numeric))[Sales Amount (Expected)], sum(cast(b.[Cost Amount (Expected)] as numeric)) [Cost Amount (Expected)], /*Item Table*/ max(c.[Inventory Posting Group]) as [Inventory Posting Group], max(c.[Item Category Code]) as [Item Category Code], max(c.[Base Unit of Measure]) as [Base Unit of Measure], sum(cast(c.[Unit Cost] as numeric)) [Unit Cost], sum(cast(c.[Standard Cost] as numeric)) [Standard Cost], sum(cast(c.[Last Direct Cost] as numeric)) [Last Direct Cost], /*WineMS Inv_Transaction tbl Table*/ max(d.[Company]) as [Company], sum(cast(d.[Quantity] as numeric)) [Quantity], sum(cast([Cost Amount] as numeric)) [WineMS Cost Amount], max(d.[Brand Group]) as [Brand Group], max(d.[Owner]) as [Owner] from [dbo].[Spier Live$Item Ledger Entry] a left join [dbo].[Spier Live$Value Entry] on b.[Item No_] = c.[No_] left join [dbo].[WineMS Inv_Transaction Tbl] on d.[Item No] = c.[No_] left join [dbo].[Spier Live$Item Ledger Entry] on a.[Item No_] = c.[No_] group by a.[Entry No_]

Errors

Msg 4104, Level 16, State 1, Line 29 The multi-part identifier "b.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 29 The multi-part identifier "c.No_" could not be bound. Msg 4104, Level 16, State 1, Line 30 The multi-part identifier "d.Item No" could not be bound. Msg 4104, Level 16, State 1, Line 30 The multi-part identifier "c.No_" could not be bound. Msg 4104, Level 16, State 1, Line 31 The multi-part identifier "c.No_" could not be bound.

Was this reply helpful? Yes No
Suggested answer

Alex A 2,857 on at

Like (0)

Report
Jason,
Here is the Join:

FROM [Spier Live$Value Entry] AS [b] LEFT JOIN [Spier Live$Item Ledger Entry] AS [a] ON [a].[Entry No_] = [b].[Item Ledger Entry No_] LEFT JOIN [Spier Live$Item] AS [c] ON [c].[No_] = [a].[Item No_] LEFT JOIN [WineMS Inv_Transaction Tbl] AS [d] ON [d].[Item No] = [c].[No_]

And here is a more exhaustive example of how I would've joined the NAV tables (I'm starting with the G_L Entry table):

Note: I'm using different Aliases

FROM [Spier Live$G_L Entry] AS [GLE] LEFT JOIN [Spier Live$G_L - Item Ledger Relation] AS [ILR] ON [ILR].[G_L Entry No_] = [GLE].[Entry No_] LEFT JOIN [Spier Live$Value Entry] AS [VE] ON [VE].[Entry No_] = [ILR].[Value Entry No_] LEFT JOIN [Spier Live$Item Ledger Entry] AS [ILE] ON [ILE].[Entry No_] = [VE].[Item Ledger Entry No_] LEFT JOIN [Spier Live$Item] AS [I] ON [I].[No_] = [ILE].[Item No_]

Regards,

Alex

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,241 Super User 2025 Season 2 on at

Like (0)

Report

from

[dbo].[Spier Live$Item Ledger Entry] a

left join [dbo].[Spier Live$Value Entry] on b.[Item No_] = c.[No_]

left join [dbo].[WineMS Inv_Transaction Tbl] on d.[Item No] = c.[No_]

left join [dbo].[Spier Live$Item Ledger Entry] on a.[Item No_] = c.[No_]

group by a.[Entry No_]

Review above code it is wrong where is b, c and d.

It should be something like below

from

[dbo].[Spier Live$Item Ledger Entry] a

left join [dbo].[Spier Live$Item] b on a.[Item No_] = b.[No_]

left join [dbo].[Spier Live$Value Entry] c on b.[No_] = c.[Item No_]

left join [dbo].[WineMS Inv_Transaction Tbl] d on d.[Item No] = c.[Item No_]

group by a.[Entry No_]

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Alex it is running now but why did you join item ledger entry with value entry on the entry no column, all 3 tables must join to the item table . I am also scared that the query will run to long , the reason for me grouping on entry no is to only add one row for a particular item but the values will be grouped as you can see by my sum(cast function, my other issue I have is why can I not use max with my posting date, I would like to group the posting date for financial year and months , is that possible in the current script I have , the query is already running at 10min , why does the query take so long

Was this reply helpful? Yes No
Suggested answer

Alex A 2,857 on at

Like (0)

Report

Hi Jason,

I was looking at it from a GL perspective as I didn't really know you're main objective, and I primarily wanted to give you an example of the proper syntax. I use the Entry column because the Value Entry Item# column doesn't always have a value depending on which G_L Account number I'm filtering by.

The Value Entry and Item Ledger Entry tables are like brother/sister tables and they generally relate best by the Entry column, but it does depend on your application. You can always use different columns for the Join if you'd like depending on what you're trying to achieve; just test your results.

Perhaps I can provide a more accurate response if you can explain the objective or the result you need to achieve. Would you be able to show us what the final report should look like, and what type of report you are trying to generate?

Additionally, if it helps any, I ran the query that I sent back to you and it finished in less than 30 seconds for me.

Was this reply helpful? Yes No
Suggested answer

Alex A 2,857 on at

Like (0)

Report

Just guessing at the goal here but maybe try starting with the Item table first, and then LEFT JOIN the other tables to that. This will eliminate any problems with the Value Entry table not having an Item number (it will just make blanks if that happens). The starting table makes a difference with Left Joins. You can start with Item and then Left Join Item Ledger (to Item) and then Left Join Value Entry (to Item Ledger). Or you might start with Item and then Left Join both tables back to Item.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

What is your exact reporting requirement, accordingly we can suggest the related query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alex , I would like the Item ledger Entry table to join to item on [Item Ledger Entry].[Item No_] = [No_]

and Value Entry Table to Item on [Value Entry].[Item No_] = [Item].[No_]

and [WineMS Inv_Transaction Tbl].[Item No] = [Item].[No_]

and then as per my script I group by [Entry No_]

If you can select a few columns from your NAV application and test to see what happens you can get a clearer understanding , i also understand what you are suggesting , i can also join the value entry and item ledger entry , however i will send you my full objective a bit later

Was this reply helpful? Yes No
Suggested answer

Alex A 2,857 on at

Like (0)

Report
Jason,

When you are running into problems like this it's a good idea to strip your query down (go back to basics) so you can get your JOIN straightened out first. The Join makes all the difference. The first question you have to ask yourself is what table should you start your Join with? And this depends on what you're trying to achieve.

Not knowing exactly what you're driving toward but by looking at your Select statement, I'm guessing that you are wanting to start first with the Item Ledger table. So I tested your code (after reducing to a more basic approach and removing all the aggregate functions), it then became obvious that trying to Join the Value Entry table ON [VE].[Item No_] = [ILE].[Item No_] is causing the long run time. My question to you is why do you think you need to join this table on this field?

When I corrected the Join between these two tables the query finishes in 23 seconds without issue.

Take a look below, take this code and start over, testing the query each time you Join another table to make sure the Join is correct.

SELECT /*Item Ledger Entry Table*/ [ILE].[Entry No_], [ILE].[Source No_], [ILE].[Global Dimension 1 Code] AS [Brand Class], [ILE].[Country_Region Code] AS [Country Code], [ILE].[Quantity] AS [Quantity], [ILE].[Remaining Quantity] AS [Remaining Quantity],[ILE].[Variant Code] AS [Variant Code], /*Value Entry Table*/ [VE].[Posting Date], [VE].[Inventory Posting Group], [VE].[Global Dimension 1 Code] AS [Brand Class Code], [VE].[Valued Quantity] AS [Valued Quantity], [VE].[Sales Amount (Actual)] AS [Sales Amount (Actual)], [VE].[Cost Amount (Actual)] AS [Cost Amount (Actual)], [VE].[Expected Cost] AS [Expected Cost], [VE].[Sales Amount (Expected)] AS [Sales Amount (Expected)], [VE].[Cost Amount (Expected)] AS [Cost Amount (Expected)], /*Item Table*/ [I].[Inventory Posting Group] AS [Inventory Posting Group], [I].[Item Category Code] AS [Item Category Code], [I].[Base Unit of Measure] AS [Base Unit of Measure], [I].[Unit Cost] AS [Unit Cost], [I].[Standard Cost] AS [Standard Cost], [I].[Last Direct Cost] AS [Last Direct Cost] /*WineMS Inv_Transaction tbl Table*/ -- [WITT].[Company] AS [Company], [WITT].[Quantity] AS numeric) AS [Quantity], [Cost Amount] AS [WineMS Cost Amount], --[WITT].[Brand Group] AS [Brand Group], [WITT].[Owner] AS [Owner] FROM [Spier Live$Item Ledger Entry] [ILE] LEFT JOIN [Spier Live$Value Entry] [VE] ON [VE].[Item Ledger Entry No_] = [ILE].[Entry No_] -- THIS IS HOW YOU SHOULD JOIN THESE TABLES ------------------CAUSES THE LONG WAIT-----------------ON [VE].[Item No_] = [ILE].[Item No_] LEFT JOIN [Spier Live$Item] [I] ON [I].[No_] = [ILE].[Item No_] --LEFT JOIN [WineMS Inv_Transaction Tbl] -- ON [WITT].[Item No] = [ILE].[Item No_]

Regards,

Alex A

Was this reply helpful? Yes No