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)