How to get count & compare of two tables with same id value

[itsdarshan]

Dear All,

this is my table
ex:

Tabl:1
p_id       product_name      stock       low_stock_alert
1        pant                      100           10
2        shirt                      150            25

tabl:2
id       p_id            Quantity
1        1                     2
2        2                     15
3        1                     4
4        1                     8
…
and so on

if for example pant have 100 pies in stock and if tabl:2 booked pant is above 90 (quantity) show low stock message…

any suggestion please…?

[shruti]

Try this… Might help

SELECT *
FROM Tabl:1
WHERE p_id IN (
SELECT p_id
FROM tabl:2
GROUP BY p_id
HAVING COUNT(*) > 1
)

[itsdarshan]

thanks for your replay…

i want see if tabl:1 pant in 100 stock

if user booked pant (tabl:2) assume he entered quantity is 90 for tabl:2
then i want to show alert…(less stock depending on low stock alert)

[astonecipher]

One, you would need to be able to differentiate when the orders were placed.

Lets say you have 100 shirts, and an order comes in to buy 99. Low stock level goes off and you order 99 more. Now someone orders 50 shirts, you would have 50 left, but you have no way to know when you restocked, or when the orders were placed. So you need to capture those things as well.

If SQLFiddle would come up, I could show you a query…

[astonecipher]

Schema (MySQL v5.6)

create table product (
  p_id int auto_increment primary key,
  product_name varchar(20),
  stock int,
  low_stock_alert int
  ) engine = InnoDB;
  
  INSERT INTO product (product_name, stock, low_stock_alert) VALUES
  ('pant', 100, 10),
  ('shirt', 150, 25);
  
  create table product_order (
  id int auto_increment primary key,
  p_id int,
  quantity int
   -- FOREIGN KEY (p_id) REFERENCES product(p_id)
  ) engine = InnoDB;
  
  insert into product_order (p_id, quantity) VALUES
  (1, 2),
  (2, 15),
  (1, 4),
  (1, 8);

---

Query #1

select 
	p.p_id
    , p.product_name
    , p.stock
    , sum(po.quantity) as orders
    , p.stock - sum(po.quantity) as on_hand
   from product p
   INNER JOIN product_order po
   ON p.p_id = po.p_id
   GROUP BY p.product_name;

p_id	product_name	stock	orders	on_hand
1	pant	100	14	86
2	shirt	150	15	135

---

[itsdarshan]

astonecipher

thank you so much… You made my day…