Advanced Select

We have 4 tables:

mysql> desc Products;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| product_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| product    | varchar(30) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc Vendors;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| vendor_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| vendor    | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc Pri开发者_开发知识库ces;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| price_id   | int(11) | NO   | PRI | NULL    | auto_increment |
| vendor_id  | int(11) | NO   | MUL | NULL    |                |
| product_id | int(11) | NO   | MUL | NULL    |                |
| price      | double  | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc Bought;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| bought_id  | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | NO   | MUL | NULL    |                |
| date       | date    | YES  |     | NULL    |                |
| pieces     | int(11) | YES  |     | 1       |                |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

now we need some complex select statements, to get the tables we need:

first table need columns [vendor, product, price, vendors(that offers the product)].

second table should show what was bought between $date1 and $date2 [product, pieces, vendor, price, date]

last table should show what could've been saved in the given time [vendor(the cheapest vendor for the product), product, pcs, price(for one product), sum(price for n products)].

As this wouldnt be complicated enaugh, the resulting tables have to show the names, instead of a key. We were sitting on this the hole day, but none of us have the knowledge to perform needed searches, so any help would be greatly appreciated.

Look into joins for selecting data from multiple tables:

SELECT * FROM Prices LEFT JOIN (Vendors, Products)
                 ON (Products.product_id=Prices.product_id AND Vendors.vendor_id=Prices.vendor_id)