1. Registered User
Join Date
May 2012
Posts
137

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:
Code:
```Goodname     ratio
Red_rose        3
Yellow_ros      5
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:
Code:
```Good name     quantity     date_done
Red_rose         50        14/3/2013
Yellow_rose      75        14/3/2013
Red_rose         80        18/3/2013
Yellow_rose      65        19/3/2013
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:
Code:
```Good name       quantity    ratio    flowerconsumed  remaining

Red_rose            130       3            84           46

Yellow_rose         140       5           140            0

Query2:
Code:
```Good name        quantity   remaining
Red_rose            130        46
Yellow_rose         140         0

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
As I see it you have at least 3 tables
1 defines stick items (eg flowers)
1 defines product (eg flowers & flower baskets)
1 defines sales
arguably you have a 4th table which defines products sold to a customer in sales

the extra table for products allows you to map how many of each type of rose foes into the product (you'd probably want two tables to cover that one defiens the product, the other defines what gozinto the product). youd also need to define now many stock items go into each product even if you sell a single flower it will need a one to one mapping.

you sales detail recordds how many of each type of product is sold

when you need to create your breakdown then you query the sales to find product sold, by joining to the product table and the product comprises table you cna expand the individual sales into their constituent components

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•