Join -- NOT (A2007)

Question

I have a classic two-listbox form construct (Move a row from this box to that box...) I want the box to the left to contain all rows that are NOT in the box on the right.

The underlying data is in a many-to-many relationship. PartNbr -- XREF -- Source, where a part may have 0 or more sources...so I have three tables, Item Master, XREF, and Source (aka "TP") table. The box on the right selects all partnbrs from the Item Master that have records in the XREF table, and shows them in the box on the right.

The box on the left should show all other parts in the TP (Third Party)item master (Sourcable parts) that are NOTin the box on the right. Since it's a many-to-many relationship, I've used the SELECT DISTINCT construct. Herewith the rowsources for the two boxes:

Left ("From") box:

SELECT DISTINCT tblTPitemMaster.TPItemID, 
     tblTPitemMaster.TPItemVendorID, 
     tblTPitemMaster.TPItemNbr, tblTPitemMaster.TPItemDescription, 
     tblTPitemMaster.TPItemUnitCost, tblXrefItemTPItem.TPXRCPASItemID 
FROM tblTPitemMaster 
LEFT JOIN tblXrefItemTPItem 
     ON tblTPitemMaster.TPItemID = tblXrefItemTPItem.TPXRTPItemID  
WHERE ((TPItemVendorID=590) AND (TPXRCPASItemID <> 10546))  
ORDER BY tblTPitemMaster.TPItemVendorID

Right ("To") Box:

SELECT tblXrefItemTPItem.TPXRPreferredVendor AS Rank, 
     tblVendor.[Vendor Name], tblTPitemMaster.TPItemNbr, 
     tblTPitemMaster.TPItemDescription, tblTPitemMaster.TPItemUnitCost, 
     tblXrefItemTPItem.TPXRID 
FROM tblVendor INNER JOIN (tblTPitemMaster 
INNER JOIN tblXrefItemTPItem 
     ON tblTPitemMaster.TPItemID = tblXrefItemTPItem.TPXRTPItemID) 
     ON tblVendor.ID = tblTPitemMaster.TPItemVendorID  
WHERE TPXRCPASItemID=10546   
ORDER BY tblXrefItemTPItem.TPXRPreferredVendor

Problem is, I get every TPItem master record in the left box. Must be something simple. Ideas?

---

Jim

Answer 1

All items that are not in the junction table?

LEFT JOIN tblXrefItemTPItem 
     ON tblTPitemMaster.TPItemID = tblXrefItemTPItem.TPXRTPItemID 
WHERE tblXrefItemTPItem.TPXRTPItemID Is Null

BTW the query would be a lot easier to read with aliases:

SELECT DISTINCT m.TPItemID, 
     m.TPItemVendorID, 
     m.TPItemNbr, m.TPItemDescription, 
     m.TPItemUnitCost, x.TPXRCPASItemID 
FROM tblTPitemMaster m
LEFT JOIN tblXrefItemTPItem x
     ON m.TPItemID = x.TPXRTPItemID  
WHERE x.TPXRTPItemID Is Null
AND TPItemVendorID=590
ORDER BY m.TPItemVendorID

I think TPXRCPASItemID comes from the junction table and so is irrelevant.