Correlated subquery structure in MS Access SQL

Question

I'm close, but I cannot seem to figure out this SQL query. I've got the SELECT and related FROM tables right, but I think my subquery structure is messed up.

Question: Compose an SQL statement to generate a list of two least expensive vendors (suppliers) for each raw material. In the result table, show the following columns: material ID, material description, vendor ID, vendor name, and the supplier's unit price. Sort the result table by material ID and supplier’s unit price in ascending order. Note: If a raw material has only one vendor (supplier), that supplier and its unit price for the raw material should also be in the result (output) table.

Here's what I've got:

SELECT Supplies_t.Material_ID, Raw_Materials_t.Material_Description, 
Vendor_t.Vendor_ID, Vendor_t.Vendor_name, Supplies_t.Unit_price
FROM Supplies_t S1, Raw_Materials_t, Vendor_t
WHERE Vendor_t.Vendor_ID = Supplies_t.Vendor_ID
AND Supplies_t.Material_ID = Raw_Materials_t.Material_ID
AND Supplies_t.Unit_price IN
  (SELECT TOP 2 Unit_price
  FROM Supplies_t S2
  WHERE S1.Material_ID = S2.Material_ID
  ORDER BY S2.Material_ID ASC, S2.Unit_price ASC)

Answer 1

Using the correct table aliases may solve your problem. You should also use explicit JOIN syntax:

SELECT s.Material_ID, rm.Material_Description, v.Vendor_ID, v.Vendor_name, s.Unit_price
FROM (Supplies_t s INNER JOIN
      Raw_Materials_t rm
      ON s.Material_ID = rm.Material_ID
     ) INNER JOIN
     Vendor_t v
     ON v.Vendor_ID = s.Vendor_ID
WHERE s.Unit_price IN (SELECT TOP 2 s2.Unit_price
                       FROM Supplies_t s2
                       WHERE s.Material_ID = s2.Material_ID
                       ORDER BY s2.Material_ID ASC, s2.Unit_price ASC
                      );