I have two tables the product file table and the packaging component table. Pnum (product number is a primary key in both tables) ComponentPNum is also a primary key in the packaging component table.



What I am trying to do is look up the cost of DrumNum by matching the DrumNum to the Pnum in the same table. However, some Drums do not have a lid as a component. The following code only shows drums that have lids, I have tried several different joins.

Would someone be able to help.

SELECT DISTINCTROW PRODFIL2.PNAME1 AS [Product Name], PRODFIL2.PNUM AS [Product #], PRODFIL2.UP AS [Packaging Unit], PRODFIL2.DRMNUM AS [Container #], ProductToPkg.lcost AS [Container Cost], PRODFIL2_PackagingComponent.ComponentPNUM AS [Lid Part #], PRODFIL2_1.lcost AS [Lid Cost]FROM ((PRODFIL2 INNER JOIN PRODFIL2 AS ProductToPkg ON PRODFIL2.DRMNUM = ProductToPkg.PNUM) INNER JOIN PRODFIL2_PackagingComponent ON PRODFIL2.PNUM = PRODFIL2_PackagingComponent.PNUM) INNER JOIN PRODFIL2 AS PRODFIL2_1 ON PRODFIL2_PackagingComponent.ComponentPNUM = PRODFIL2_1.PNUM;