title: Database Modeling: query for comparison shopping site
body: <p>Say I am building a comparison shopping site. This means I have multiple vendors selling the same item. Assume a basic database structure like the following:</p>
  
  <pre><code>==================================
  vendor_product
  ==================================
  vendor_product_id (PK)
  condition ('new', 'used', 'collectible')
  unit_price
  
  
  ==================================
  product_mapping
  ==================================
  vendor_product_id
  product_id
  
  
  ==================================
  product
  ==================================
  product_id (PK)
  product_code
  name
  </code></pre>
  
  <p>Given a product_code (or list of product_code), return all vendor products that map to the appropriate product, grouping the vendor products by the mapped product, displaying price range (lowest to highest, regardless of condition), and number of vendors selling the product. The output would be something like this:</p>
  
  <pre><code>======================================================================
  product_code | name    | vendor_count | lowest_price | highest price |
  ======================================================================
   abc123      | Sony TV | 5            | 200          | 350           |
  ----------------------------------------------------------------------
   xyz987      | Barbie  | 11           | 15           | 22            |
  ----------------------------------------------------------------------
  </code></pre>
  
  <p><strong>I'm not sure how to do the SQL for this. Any suggestions?</strong></p>