Create a query joining ShipTo and CustCnt

Epicor ERP 10
,
1

I am struggling to create a query that joins ShipTo and CustCnt. I am trying to find the Ship To records that do not have any customer contacts associated. When I add CustCnt it does not naturally join in the BAQ. I added table relations Company and CustNum and then tried again adding ShipToNum and neither is giving the desired results. Any ideas?

2

CustContacts.baq (21.7 KB)
Good afternoon,
This BAQ starts with the customer table. To this we join the ShipTo table and the CustCnt table. If you add in this order, the joins should be created automatically. Then show the customer information, shipto information, and a count of the contacts. Then you can see if any are 0.
Here is the SQL:


select 
	[Customer].[Name] as [Customer_Name],
	[ShipTo].[CustNum] as [ShipTo_CustNum],
	[ShipTo].[ShipToNum] as [ShipTo_ShipToNum],
	[ShipTo].[Name] as [ShipTo_Name],
	(count(CustCnt.ConNum)) as [Calculated_Contacts]
from Erp.Customer as Customer
inner join Erp.CustCnt as CustCnt on 
	Customer.Company = CustCnt.Company
	and Customer.CustNum = CustCnt.CustNum
inner join Erp.ShipTo as ShipTo on 
	Customer.Company = ShipTo.Company
	and Customer.CustNum = ShipTo.CustNum
group by [Customer].[Name],
	[ShipTo].[CustNum],
	[ShipTo].[ShipToNum],
	[ShipTo].[Name]

Good luck!

3

Thank you