Obodo

Thanks

like i thought, the ifnull did the magic. i was able to get the qty_remaining but i couldnt get the qty_bot to be 0. still showing null. i guess i just have to stick with the null of is there a way i could get 0 and not null? SELECT a.prod_id, a.prod_name , a.qty_received , b.qty_bot , a.qty_received - ifnull(b.qty_bot, 0) as qty_remain FROM ( SELECT prod_id , prod_name , sum(prod_qty) as qty_received FROM tbl_distribution WHERE staff_id = 2962 GROUP BY prod_id ) a LEFT JOIN ( SELECT prod_id , sum(qty_bought) as qty_bot FROM tbl_sales_bar WHERE staff_id = 2962 GROUP BY prod_id ) b USING (prod_id);

Thanks for help and pointers. your method seems easier but for the null value on goldberg. i am going to work on the normalization of the tables. i will like to adapt to your method. how can the null issue be resolved? i am thinking might ifnull do the magic? lemme give it a shot though

woah! thanks a lot. this one accounts for the 10 goldberg that was not sold

I just noticed something on this. the goldberg ought to remain 10 but its null. since nothing is sold from the quantity received, the quantity remaining should be be quantity received

SELECT a.prod_name, a.prod_size, b.prod_name, b.prod_size, ifnull(b.qty_bot,0), ifnull(a.qty_received ,0) from ( SELECT a.prod_id prod_id FROM tbl_distribution a UNION SELECT b.prod_id FROM tbl_sales_bar b ) t LEFT JOIN ( SELECT a.prod_id, a.prod_name, a.prod_size, SUM(a.prod_qty) qty_received FROM tbl_distribution a WHERE a.staff_id = 2962 GROUP BY a.prod_id ) a on a.prod_id = b.prod_id LEFT JOIN ( SELECT b.prod_id, b.prod_name, b.prod_size, SUM(b.qty_bought) qty_bot FROM tbl_sales_bar b WHERE b.staff_id = 2962 GROUP BY b.prod_id ) b on b.prod_id = t.prod_id" Gave an error

OK. But it's the same issue I am trying to solve but with a different approach. I am just wondering why it did not work. Maybe I should just let go and stick with what you gave. Just wanted to learn more and feed my curiosity

Sorry for the images used. just wanted to drive home my intentions. Noted though. I will try and normalize the table more. I am just trying to avoid too many JOINs. Thank you so very much cos this solved my question. There is something else i was trying but was getting error though. I was thinking a UNION could achieve same result? Dont want to start another discussion since the issue is solved else i would have posted my code so you could see and maybe tell what is wrong with it? Can i post the code? Thanks anyways

I want to sum and join 2 tables. I am getting a wrong answer. What am i doing wrong please. what i want is to get the sum of quantity bought and the sum of product quantity so that when i minus the product quantity for the quantity bought, i will get the balance which is the difference between the product quantity - quantity bought = available quantity $stmt = $pdo->query(" SELECT a.prod_name, a.prod_size, b.prod_name, b.prod_size, SUM(b.qty_bought) qty_bot, SUM(a.prod_qty) qty_received FROM tbl_distribution a JOIN tbl_sales_bar b ON a.staff_id = b.staff_id WHERE a.staff_id = '$_GET[id]' GROUP BY b.prod_id "); WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){ echo '<tr> <td>'.$row["prod_name"].'</td> <td>'.$row["prod_size"].'</td> <td>'.$row["qty_received"].'</td> <td>'.$row["qty_bot"].'</td> <td></td> </tr>'; } Image shows what i want to achieve tbl_distribution - the table for distribution tbl_distribution_user - the table for a single user after sum and group tbl_sales_bar - table for sales tbl_sales_bar_user - table for a single user in bar after sum and group result - what i intent to achieve as my end result after summing and grouping thanks

Sorry. I've been away for a while. Thanks all. I will do as suggested. ps btw: i have been able to do the reset and delete as i planned initially but wont be implementing it. i will go with your suggestion. Thanks

Thanks. But I am not totally deleting the data, i am moving them to a different table. I am still trying to comprehend the issue of not updating the records since the goods are returned. if we don't update the record, it means the record we have is not correct as the returned goods will be taken to the store for reselling. so we will have an excess that is not reflecting on the record. how do you deal with that? unless of course we do a different record and collation and i feel is one-to-many exercise. what do you think?

Hello all, In my shopping app. I want add a delete feature from the admin area. In case there is a dispute for a transaction, the admin can delete the transaction. The problem I am having is that when the transaction is deleted, i want the items bought to be added to their respective total. i.e if i have 200 pepsi, 30 cocacola, 20 fruit juice in stock and a customer bought 2 pepsi, 2 cocacola, 3 juice. definately the total in stock will reduce. I want a situation where after i delete the transaction the items bought will be added back to its remaining quantity. previous quantity pepsi 200 coke 30 fruit 20 after purchase pepsi 180 coke 28 fruit 17 after delete pepsi 200 coke 30 fruit 20 thanks

In my database table, i have a field for price and quantity. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price and quantity and if I select retail, it will bring the price and the quantity.

I'm thinking aloud. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price per carton and the quantity available and if I select retail, it will bring the price per unit and the quantity available for the item. The values will be from the database of course I'm thinking jquery will be able to do such.

WOAH! I'm lost here! Will need to read and reread. Thanks

Exactly! Inventory is the main headache

WOAH! I'm lost here! Will need to read and reread. Thanks

What I have in mind is to convert all the cartons to pieces. But I envisage a problem of conflict somewhere. I have not been able to put the problem to perspective though. I want to do the thinking and computation first before I start codifying it.

My thoughts are almost like yours.

pls i am looking for the best way to get this done: i want to be able to deal with both retail and wholesales from one spot. in my frontend store, a customer can decide to chose buying just an item or the whole pack. e.g i want to buy noodles, when i type noodles, it will give me an option to buy either 1 or 2 or the whole carton or also i can buy a carton and some pieces. meanwhile any item bought from the front end should be deducted from the quantity in stock (e.g a cartoon of noodle contains 15 pieces and i have 30 cartons of noodle in stock. if a carton and 2 pieces are bought, it should deduct the item i.e 30 cartons minus 1 carton and 2 pieces = 28 cartons and 13 pieces as the remain stock on noodles) thanks

ok sir. how can that be done with the above mentioned?

thanks all. i got it up and running now. but Mr Barand, is there a way i could have stored the items into the database without the use of form? meanwhile like i tried explaining, the values are derived from session so the page i had problem is the checkout page. thanks again

I really want to update the record. I mean to subtract the quantity of items bought to that in stock. that is if a customer buys 12 coke and 15 milk, they will be deducted from the total in stock. hope you get it. thanks

Sorry but i don't quite get you. the files are read only cos user is not expected to do any form of input on the page. the values are from another page which is the front end. this page is the checkout page.

Hahahahahahaha. Users don't get to input their own price. this page is actually to send the order to the database and print. meanwhile its for online, I'm developing it for my wife to use in her shop. so everything is ran on localhost.