I suppose this is a hard question and I hope you could help to find a solution,
In a flower store there are two tables. Table1, goods properties and table2 showing the number of goods /products bought and sold in/out of the store. The store sells flowers in baskets, so the baskets are considered the unit of sales as the final product that goes out of the store. In other words each basket shows how many flowers are sold depending on the mentioned ratio:
Table1:
The ratio describes how many of the flower is consumed in each basket. For example 3 red roses and 5 yellow roses are put and deducted from the store when one basket of flower is sold.
Table2:
As you see 130 is the overall input of redrose, while 28 baskets are sold which will deduct the quantity of red rose.
My question is that how can we have this query to show us all the transactions at a view?
Query1:
Query2:
As you see 28 baskets are sold, and the ratio of red rose is 3, that means in each basket 3 red roses are sold. This means 28*3 = 84 red roses are sold and 46 is the remaining in the store. I don’t know how I can make the appropriate query to show the above results.
Thank you
In a flower store there are two tables. Table1, goods properties and table2 showing the number of goods /products bought and sold in/out of the store. The store sells flowers in baskets, so the baskets are considered the unit of sales as the final product that goes out of the store. In other words each basket shows how many flowers are sold depending on the mentioned ratio:
Table1:
Code:
Goodname ratio
Red_rose 3
Yellow_ros 5
Blue basket
Green basketThe ratio describes how many of the flower is consumed in each basket. For example 3 red roses and 5 yellow roses are put and deducted from the store when one basket of flower is sold.
Table2:
Code:
Good name quantity date_done
Red_rose 50 14/3/2013
Yellow_rose 75 14/3/2013
Blue basket 10 16/3/2013
Red_rose 80 18/3/2013
Yellow_rose 65 19/3/2013
Bluebasket 5 20/3/2013
Green basket 8 20/3/2013
Green basket 7 21/3/2013My question is that how can we have this query to show us all the transactions at a view?
Query1:
Code:
Good name quantity ratio flowerconsumed remaining
Red_rose 130 3 84 46
Blue basket 15 45
Green basket 13 39
Yellow_rose 140 5 140 0
Blue basket 15 75
Green basket 13 65Query2:
Code:
Good name quantity remaining
Red_rose 130 46
Yellow_rose 140 0
Blue basket 15
Green basket 13As you see 28 baskets are sold, and the ratio of red rose is 3, that means in each basket 3 red roses are sold. This means 28*3 = 84 red roses are sold and 46 is the remaining in the store. I don’t know how I can make the appropriate query to show the above results.
Thank you
