[crystal2005]

Hi,

I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following

1. The wine name, grape variety, year, winery, and region
2. The minimum cost of wine in the inventory
3. The number of bottles available at the minimum price
4. The total number of bottles available at any price
5. The total number of unique customers who purchased the wine (at any price)
6. A breakdown of the sales revenue, stock sold, and unique customers for each price that the wine can be sol for.

So, far I have done successfully to join those queries in a single table till the fourth within one SQL query

SELECT wine.wine_name, grape_variety.variety, wine.year, winery.winery_name, region.region_name, inventory.cost, inventory.on_hand, SUM(inventory.on_hand) FROM wine INNER JOIN grape_variety INNER JOIN wine_variety ON wine.wine_id = wine_variety.wine_id AND wine_variety.variety_id = grape_variety.variety_id JOIN winery ON wine.winery_id = winery.winery_id JOIN region ON region.region_id = winery.region_id JOIN inventory ON inventory.wine_id = wine.wine_id GROUP BY inventory.wine_id, grape_variety.variety_id;

I have done the fifth query in a single SQL query, without having connected with the last four previous queries.

SELECT wine_id, COUNT(items.cust_id) from items GROUP BY wine_id;

I encountered problem to join the fifth query into the above complex query. The table rows appeared to be imbalance.

Winestore database data is available from this link, if you wish to have a look http://stevenryan.co.cc/source/other/winestore.data

Any help leading to my success to join all those six queries into a single table and in single SQL query would be appreciated. Thank you so much.