[SOLVED] SELECT query including a junction table

SELECT query including a junction table

I have three tables:

Product

Product_ID
Name
Description
Price

Supplier

Supplier_ID
Name
Location

ProductSupplier

Product_ID
Supplier_ID

ProductSupplier is the junction table instead of having the many to many relationship.

I need to create a SELECT statement that will return two columns: the name and price of the product (not the product ID), but only if the Supplier is located in Australia. The supplier's location can't show up in the result.

I would know how to do this without the junction table, but this has stumped me.

Solution

the following sql statement will return all products has at least Supplier located in Australia

select distinct p.Name,p.Price
from Product  p
inner join ProductSupplier  ps on ps.Product_ID = p.Product_ID
inner join Supplier  s on s.Supplier_ID = ps.Supplier_ID
where s.Location = 'Australia'