Unanswered: 2 foreign keys in one table to the same primary key of another table
Hi. I have a database in Access. tables ProductMovement and ProductLocations are attached in jpg. ProductMovement has 2 fields LeaveProductLocation and EnterProductLocation. Both of them are foreign keys to the ProductLocation table.
I'm having trouble with a SELECT statement for ProductMovement. I need to do it with an input parameter for ProductMovement.EnterProductLocation.
I believe that it is 'inner join' issue... i cannot handle multiple inner joins especially with 2 foreign keys.
I'm using this but 2nd bolded column here returns just ID and when i try to 'inner join' this as well, it returns no rows...
"SELECT ProductMovement.ID, Products.Name, Vendors.Name, ProductMovement.Quantity, Measures.Name, ProductMovement.UnitPrice, ProductLocations.Name, ProductMovement.EnterProductLocationID, ProductMovement.LeaveDate, ProductMovement.EnterDate
FROM ProductMovement, Products, Vendors, Measures, ProductLocations
WHERE Products.ID=ProductMovement.ProductID AND
ProductLocations.ID=ProductMovement.LeaveProductLocationID AND ProductMovement.EnterProductLocationID=@ID
Unless I'm mis-reading the SQL (which is possible - I tend to use JOIN statements in the FROM clause), you're not joining ProductMovement.EnterProductLocationID to ProductLocations. Could that be the problem?
weejas, even if i create the query using Access' own wizard through INNER JOIN, those 2 fields are not still displayed...
i wonder common query when 2 foreign keys from one table reference the same primary key in another table. if i could see an example, i believe i can handle my code.
You need to refer to the ProductLocations table twice, and each instance needs to join to either the ProductLocationLeaveID field or the ProductLocationEnterID field. Otherwise, you're looking for a location ID that matches the enter and leave IDs at the same time. Short of recording a movement from the bar to the bar (for example), you're not going to find any.
Try this code:
, PLL.Name AS LeaveLocationName
, PLE.Name AS EnterLocationName
FROM ProductLocations AS PLE
INNER JOIN (ProductLocations AS PLL
INNER JOIN (Measures AS M
INNER JOIN (Vendors AS V
INNER JOIN (Products AS P
INNER JOIN ProductMovement AS PM ON P.ID = PM.ProductID)
ON V.ID = PM.VendorID)
ON M.ID = PM.MeasureID)
ON PLL.ID = PM.LeaveProductLocationID)
ON PLE.ID = PM.EnterProductLocationID;