Nested or correlated subquery SQL

Question

I have the following relational algebra task, which I need to convert into a SQL query, compatible with Microsoft SQL server:

The relevant tables are below:

Raw_Materials_t: material_ID, material_description,

Supplies_t: Vendor_ID, Material_ID, Unit_Price

Vendor_t: Vendor_ID, Vendor_Name

SELECT DISTINCT V.Vendor_ID
FROM Raw_Materials_t R, Supplies_t S, Vendor_t V
WHERE R.Material_description = 'walnut'
  AND S.Unit_price < 14
  AND R.Unit_price < 14
  AND V.Vendor_ID = S.Vendor_ID

I've been told that my query does produce the correct result, but it does not explicitly use the ordered relational algebra steps. So what I need to do is:

1. (Natural?) join Raw_Materials_t on Supplies_t selecting only rows where it's true that material_description is walnut and unit_price is less than $14
2. Take these results, project only the Vendor_ID column (discarding other columns)
3. Join this column on Vendor_t
4. Project only the Vendor_Name column from the above results.

Is this is a nested subquery or a correlated subquery? I'm not familiar with either and unsure how to proceed.

Answer 1

You do not have a join condition between Material and anything else.

This is the complete query, which is just simple joins, no correlated queries or subqueries:

SELECT distinct V.Vendor_ID 
FROM Raw_Materials_t R
join Supplies_t S on S.Material_ID=R.Material_ID
join Vendor_t V on V.Vendor_ID=S.Vendor_ID
WHERE R.Material_description = 'walnut' AND S.Unit_price < 14