Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    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...

    Code:
    "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
    Vendors.ID=ProductMovement.VendorID AND 
    Measures.ID=ProductMovement.MeasureID AND
    ProductLocations.ID=ProductMovement.LeaveProductLocationID AND ProductMovement.EnterProductLocationID=@ID
    Appreciate your help. Thank you!
    Attached Thumbnails Attached Thumbnails ProductMovement.JPG   ProductLocations.JPG  

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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?

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    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.
    Last edited by emilh; 06-09-09 at 01:50.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Can you post an example of your data, along with the results that you expect to see in the query? That would make it easier to understand.

  5. #5
    Join Date
    Mar 2009
    Posts
    27
    Table1: ProductLocations
    ------------------------
    ID (PK)
    Name
    ------------------------

    Table2: ProductMovement
    ------------------------
    ID
    FromProductLocationID (FK)
    ToProductLocationID (FK)
    ------------------------

    I want the result to be like this:

    ProductMovement.ID|ProductLocation.Name ('From' field)|ProductLocation.Name ('To' field)

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Table structure <> sample data :P
    Can you post a zipped copy of the database, with any sensitive information removed?

  7. #7
    Join Date
    Mar 2009
    Posts
    27
    here it is. check relationship pls. thank you!
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Got it!

    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:
    Code:
    SELECT	PM.ID
    ,	P.Name
    ,	V.Name
    ,	PM.Quantity
    ,	M.Name
    ,	PM.UnitPrice
    ,	PLL.Name AS LeaveLocationName
    ,	PLE.Name AS EnterLocationName
    ,	PM.LeaveDate
    ,	PM.EnterDate
    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;
    Good luck!

  9. #9
    Join Date
    Mar 2009
    Posts
    27
    oh weejas, thank you so much! it works very well! now i'll try to analyze this wonderful query

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •