Need a help in the query to get a desired output. Here is my Table Structure:

1. Product

   Here qty is a Stock

   id	product_name	qty
   1	Test	5

2. Purchase

   From purchase table, we have to select a date and warehouse by ID

   id	warehouse id	date
   1	1	04-03-2022

3. Purchase Item

   Inward Stock should be shown from this table

   id	product_id	qty	purchase_id
   1	1	5	1

4. Order

   From Order table, we have to select a date and warehouse by ID Here detail is a warehouse ID

   id	detail	date
   1	1	04-03-2022

5. Order Item

   Outward Stock should be shown from this table.

   id	product_id	qty	order_id
   1	1	5	1

Desired Output Should be like this:

Where P means Purchase & O means Order

QUERIES to Generate a respective tables:

db<>fiddle here

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(150) NOT NULL,
  `qty` int(10) NOT NULL,
  `price` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;


CREATE TABLE `purchase` (
  `id` int(11) NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `purchase_item` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `purchase_id` int(11) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `rate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `order` (
  `id` int(11) NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `order_item` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `rate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

INSERT INTO `products` (`id`, `product_name`, `qty`, `price`) 
VALUES
(1,  'TEST', 1, '50'),
(2, 'TEST 2', 0, '50'),
(3, 'TEST 3', 0, '50'),
(4,  'TEST 4', 0, '50')
;

INSERT INTO `purchase` (`id`, `warehouse_id`, `date`) VALUES
(1, 1, '2022-03-02');

INSERT INTO `purchase_item` (`id`, `product_id`, `purchase_id`, `qty`, `rate`) VALUES
(1, 1, 1, '2', '50');


INSERT INTO `order` (`id`, `warehouse_id`, `date`) VALUES
(1, 1, '2022-03-02');

INSERT INTO `order_item` (`id`, `product_id`, `order_id`, `qty`, `rate`) VALUES
(1, 1, 1, '2', '50');

CodePudding user response：

Here is a query which returns the format you are looking for.
I've updated it to use date and warehouse_id as parameters in the WHERE clause.

with stock_movements as
(
select 
  'P' PA,
  pu.warehouse_id ,
  pu.`date` mvtdate,
  p.id,
  concat(p.product_name,'(P)') Pname,
  pi.qty pqty,
  0 as oqty,
  sum(coalesce(pi.qty,0)) Total
from products p
left join purchase_item pi
on p.id = pi.product_id
left join purchase pu
on pu.id = purchase_id
group by 
  pu.warehouse_id,
  p.id,
  p.product_name,
  pu.`date`,
  pi.qty
union all
select
  'A',
  ord.`warehouse_id`,
  ord.`date` mvtDate,
  p.id,
  concat(p.product_name,'(O)'),
  0,
  sum(coalesce(o.qty,0)) oqty,
  sum(coalesce(pi.qty,0))
     -sum(coalesce(o.qty,0))
from products p
left join order_items o
on p.id = o.product_id
left join purchase_item pi
on p.id = pi.product_id
left join `order` ord
on ord.id = order_id
group by
  ord.`warehouse_id`,  
  ord.`date`,
  p.id,
  p.product_name
order by 
  id
)
select
PA,
warehouse_id,
mvtdate "date",
id "ID",
pname "Product Name",
sum(pqty) "Input",
sum(oqty) "Output",
sum(Total) "Total"
from stock_movements
where mvtDate = '2022-03-02'
and warehouse_Id = '1'
group by
id, pname,pa,warehouse_id
order by 
id,pa desc;

PA | warehouse_id | date       | ID | Product Name | Input | Output | Total
:- | -----------: | :--------- | -: | :----------- | ----: | -----: | ----:
P  |            1 | 2022-03-02 |  1 | TEST(P)      |     2 |      0 |     2
A  |            1 | 2022-03-02 |  1 | TEST(O)      |     0 |      1 |     1
P  |            1 | 2022-03-02 |  2 | TEST 2(P)    |     3 |      0 |     3
A  |            1 | 2022-03-02 |  2 | TEST 2(O)    |     0 |      1 |     2

db<>fiddle here

FIRST DB FIDDLE: *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a6d7e94af44035d838b2be5bf863a476