I have a query with a inner join:
it finds part 525-009-007-Custom1 and matches it to the last order 75788
However, I want to get all parts not just ones with a last order, so I change it to a left outer join:
To test, I added the specific part number to the Where clause and then the left outer join worked. But I need to get all parts so that is not acceptable obviously. Whats going on?
Hi @Evan_Purdy ,
@gpayne already pointed out the problem partially.
However it is not the BAQ really, it is how SQL works.
If you have SSMS installed, try these three:
1-
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[InActive] as [Part_InActive],
[Part].[UnitPrice] as [Part_UnitPrice],
[OrderDtl1].[RequestDate] as [OrderDtl1_RequestDate],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum]
from Erp.Part as Part
left outer join (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(max(OrderDtl.OrderNum)) as [Calculated_LatestOrderNum]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.VoidLine = 0)
group by [OrderDtl].[Company],
[OrderDtl].[PartNum]) as SubQuery1 on
Part.Company = SubQuery1.OrderDtl_Company
and Part.PartNum = SubQuery1.OrderDtl_PartNum
inner join Erp.OrderDtl as OrderDtl1 on
SubQuery1.OrderDtl_Company = OrderDtl1.Company
and SubQuery1.OrderDtl_PartNum = OrderDtl1.PartNum
and SubQuery1.Calculated_LatestOrderNum = OrderDtl1.OrderNum
2-
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[InActive] as [Part_InActive],
[Part].[UnitPrice] as [Part_UnitPrice],
[OrderDtl1].[RequestDate] as [OrderDtl1_RequestDate],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum]
from (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(max(OrderDtl.OrderNum)) as [Calculated_LatestOrderNum]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.VoidLine = 0)
group by [OrderDtl].[Company],
[OrderDtl].[PartNum]) as SubQuery1
right outer join Erp.Part as Part
on
Part.Company = SubQuery1.OrderDtl_Company
and Part.PartNum = SubQuery1.OrderDtl_PartNum
inner join Erp.OrderDtl as OrderDtl1 on
SubQuery1.OrderDtl_Company = OrderDtl1.Company
and SubQuery1.OrderDtl_PartNum = OrderDtl1.PartNum
and SubQuery1.Calculated_LatestOrderNum = OrderDtl1.OrderNum
3-
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[InActive] as [Part_InActive],
[Part].[UnitPrice] as [Part_UnitPrice],
[OrderDtl1].[RequestDate] as [OrderDtl1_RequestDate],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum]
from (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(max(OrderDtl.OrderNum)) as [Calculated_LatestOrderNum]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.VoidLine = 0)
group by [OrderDtl].[Company],
[OrderDtl].[PartNum]) as SubQuery1
inner join Erp.OrderDtl as OrderDtl1 on
SubQuery1.OrderDtl_Company = OrderDtl1.Company
and SubQuery1.OrderDtl_PartNum = OrderDtl1.PartNum
and SubQuery1.Calculated_LatestOrderNum = OrderDtl1.OrderNum
right outer join Erp.Part as Part
on
Part.Company = SubQuery1.OrderDtl_Company
and Part.PartNum = SubQuery1.OrderDtl_PartNum
You see the result, right? Why do you think the result is different?
Welcome to the SQL world 
This is why I usually use CTE
I have 0 access - old Epicor Cloud MT user. Perhaps I have fallen for the usual trap of asking to specific of a technical question. What I was really looking for was a way to see all parts in a product group, and if there was any orders for it, what was the last order date and the order number.
remove the Part table and SubQuery1, add it after SubQuery2 and do a right join. Should work that way 
I would add it for sure
We are multi-company and we need it to be there.
If you are not a multi-company setup then maybe you can skip it
Regarding the slowness, maybe revise your quey.
Please have a look at mine and try to re-construct it.
Mine was running in 1-2 seconds
Always add the Company join. It is a primary index on all tables.
I kind of disagree.
For a single-company setup this does not make any difference since it is not a primarykey or a foreignkey on any level.
But definitely a good practice.
We were single-company but then joined another entity and now we have to add all of the company joins which is very painful…Lol
This statement is a gross misunderstanding of how indexes work. Regardless of one company or many companies the company field is part of the native indexes that if you ignore it well impact performance. There are fringe situations where company can hurt you (customer table on 10.2 is one of the few scenarios) but in general company included will improve performance. Alternative would be defining your own indexes excluding company but that would seems counter productive to scale planning.
What @jgiese.wci said is correct joins should always be done on full indexes or you are forcing a full table scan
Regardless of multi-company or not. Look in the data dictionary and the indexes for a table are listed.
If you use for example a join on SysRowID then you don’t need to use company because SysRowID is a primary index by itself.
But for part the next index is Company,PartNum
So ALWAYS use a full index on a join whenever possible
This is correct
I tested this to see it in practice and on an unindexed table with 1 million rows and 2 columns, first column static and indexed the run time was almost 50% faster. Table looks like this:
This is stat for running on unindexed table:
And here is the stat for running on the same table but indexed on Column Company (all values in this column are the same and A):
Thank you @jgiese.wci and @josecgomez for educating me!
I have frequently obeserved BAQs with inner joins running so slow that they time out and then after some careful changes to outer joins the speed becomes acceptable.
I once updated some SSRS reports to use Company in all joins in a single-company on-prem 10.2.x environment. This made some of them 30 times faster. If you have SSMS, sp_helpindex is useful to see what indexes exist and which columns you should always include in joins even if you know you don’t need them for a correct result.
