SELECT query including a junction table

Question

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.

Answer 1

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'

Answer 2

If you can avoid select distinct (and count(distinct)), then that is a good idea. They incur extra overhead for removing duplicates.

So, the best approach would do the filtering in the where clause, using in or exists:

select p.Name, p.Price
from Product p
where exists (select 1
              from ProductSupplier ps inner join
                   Supplier s
                   on s.Supplier_ID = ps.Supplier_ID
              where ps.Product_ID = p.Product_ID and s.Location = 'Australia'
             );

This should have the best execution plan.