SELECT query including a junction table

1
votes

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.

2
sqloracleselectjunction
It seems like a product can have many suppliers. Are you looking for products that have at least one supplier that isn't in Australia, or for products that don't have a supplier in Australia at all? – Mureinik

2 Answers

1
votes

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.

-1
votes

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'