Hi all, I'm working on creating a SQL query for a receiving label we use. All the data fields are easy enough to pull form the PurchLine table except for INVENTBATCHID (Batch numebr) from the InventDim table.
My current query is as follows. It works, but it takes over 3 minutes to pull the info and thus unusable. Is there a better JOIN I should be using?
I get pretty confused when it comes to any of these 3 tables: InventItemGTINStaging, InventDimStaging, InventTransOriginStaging. I understand they have to do with inventory transactions but I just can't seem to map it out in my head or SSMS...
SELECT
PrnPurchLineStaging.PURCHID,
PrnPurchLineStaging.ITEMID,
PrnPurchLineStaging.VENDACCOUNT,
InventItemGTINStaging.GLOBALTRADEITEMNUMBER,
InventDimStaging.INVENTBATCHID
FROM
PrnPurchLineStaging
INNER JOIN InventItemGTINStaging ON PrnPurchLineStaging.ITEMID = InventItemGTINStaging.ITEMID
LEFT JOIN InventTransOriginStaging ON InventTransOriginStaging.INVENTTRANSID = PrnPurchLineStaging.INVENTTRANSID
LEFT JOIN InventDimStaging ON InventDimStaging.INVENTDIMID = InventTransOriginStaging.INVENTDIMID
WHERE
PrnPurchLineStaging.PURCHID = 'PUR20-008027'
