On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote:
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
> is an easy/efficient way to gather the following data from a single query.
>
> Example Tables
>
>
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
>
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
>
>
> I would like to obtain the least cost of each product type and its
> associated vendor.
>
> So...I would like to see a result similiar to the following:
>
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm
> not really concerned with which vendor is chosen in the result set here.)
>
>
Try this:
select name, product_type, min(cost) from vendors join products on
vendors.id = products.vendor_id group by product_type;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
