Search code examples
sqloracleselectjunction

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'