MySQL query Problem for 2 tables?

Question

I have 2 tables , orders and sold :

Table orders :
id product quantity :
1 bag 10
2 shoes 10
3 cloth 5
4 hat 15

I sold bag 10 pcs , shoes 5 pcs , then inserted data in the table "sold" :
id quantity
1 10
2 5

So I want to make MySQL php script which should show the products which are not sold yet as follows :

id product quantity
2 shoes 5
3 cloth 5
4 hat 15

Thank you

Answer 1

SELECT id,

product,

(quantity-coalesce(sold_quantity,0)) AS quantity

FROM

(

SELECT id,

product,

quantity,

(

SELECT sum(quantity) AS sold_quantity

FROM sold

WHERE id = orders.id

) AS sold_quantity

FROM orders

) AS foo

WHERE (quantity-coalesce(sold_quantity,0)) > 0



this will work, however, i see that the column "id" doesn't refer to the row id, it referes to the product id, i think the structure of your sold data needs to look like this (id, product_id, quantity_sold) to be more descriptive.

Answer 2

Hi I think what your your problem is is your looking at it from the wrong point of view



What you need is two tables tbl_stock which holds the stock quantity and tbl_sales which olds the number you sell



tbl_stock

1,bags,10

2,shoes,10

3,hats,15

4,socks,15



tbl_sales



This is where you hold the the number of items sold per transaction



each time a bag is sold



tbl_sales



1,bag,0 then a sales happens 1,bag,1 then another sale happens 1,bag,2 lets say you sell 5 bags in that week

1,bag,5



you then write the script linking id <===> id



you then tbl_stock - tbl_sales for id<===> id



that way you have control



if tbl_stock - tbl_sales < 4 then "Time to restock"

if tbl_stock - tbl_sales = 0 then "Sold Out"



or just remaining = tbl_stock - tbl_sales



eg.



[tbl_stock].[count] - [tbl_sales].[count] 10-5 = 5



for your bags



where count being the number field in each of the rows



I am sure you know about unique ID etc so I mean the stock.



There are other ways such as just removing the the count each time from tbl_stock

------------------------------------------------------------------------------

However you could do the same thing in one table





tbl_Stock



1,Bag,10,(x)

2,Shoes,15,(x)



Where (x) is incremented at each sales you then just take [tbl_stock].[field3] - [tbl_stock].[field4]



and create the same above If conditions.



Personally I would go for this one as only one table is nessesary to refernece with no outjoins speeding up the process.



that way tbl_stock stands on it own as I have one set like this



tbl_stock



1,Cat 5e cable,2000,650,200,10,.99



field1 = ID (unique key)

field2 = Descritption

field3 = Maximum Stock Quantity

field4 = Sold quantity

field5 = Restock at quantity

filed6 = Minimum sale quantity

filed7 = cost per meter



The rest you use your script to tell you what's going on and other to update that field as and when.

Answer 3

I'm assuming your id field in the "sold" table refers to the id of the item in the "orders" table. If not you need to add an id_stock or id_orders field to your "sold" table.



The script is not difficult.



Something like



Select * from sold where quantity > 0

Answer 4

while you're using MySql, are you using it on the side of Hypertext Preprocessor? you need to use UNION to connect decide on statements (decide on prod,volume FROM table1) UNION (decide on prod,volume FROM table2) UNION (decide on prod,volume FROM table3) you may then use Hypertext Preprocessor to loop in the process the tables. this ought to provide you a concept of a thank you to proceed. the whole project sounds complicated.