Member Avatar for aynamohol

I have two tables named stockin and stockout. I can execute query easily separately for each table. Now, I want to bind both query in a table. Please check query and output below-

query in stockin:

select serialno, sum(in_quantity) SUMIN, group_concat(in_quantity) GCIN from stockin group by stockin.serialno

output:

+----------+-------+-------------+
| serialno | SUMIN | GCIN        |
+----------+-------+-------------+
| AAA1     |   800 | 500,200,100 |
| AAA2     |   500 | 500         |
| AAA3     |     3 | 1,1,1       |
+----------+-------+-------------+
3 rows in set (0.00 sec)

query in stockout:

select serialno, sum(out_quantity) SUMOUT, group_concat(out_quantity) GCOUT from stockout group by stockout.serialno

output:

+----------+--------+-------------------------+
| serialno | SUMOUT | GCOUT                   |
+----------+--------+-------------------------+
| AAA1     |    740 | 300,100,50,50,20,20,200 |
| AAA3     |      3 | 1,1,1                   |
+----------+--------+-------------------------+
2 rows in set (0.00 sec)

Now, I would like to create a report as belows-

+----------+-------+-------------+----------+--------+-------------------------+
| serialno | SUMIN | GCIN        | serialno | SUMOUT | GCOUT                   |
+----------+-------+-------------+----------+--------+-------------------------+
| AAA1     |   800 | 500,200,100 | AAA1     |    740 | 300,100,50,50,20,20,200 |
| AAA2     |   500 | 500         | (NULL)   |  (NULL)| (NULL)                  |
| AAA3     |     3 | 1,1,1       | AAA3     |      3 | 1,1,1                   |
+----------+-------+-------------+----------+--------+-------------------------+
3 rows in set (0.00 sec)
  • 2 Contributors
  • 1 Reply
  • 3K Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Something like this:

select 
    stockin.serialno, 
    sum(in_quantity) SUMIN, 
    group_concat(in_quantity) GCIN, 
    sum(out_quantity) SUMOUT, 
    group_concat(out_quantity) GCOUT
from stockin 
left join stockout 
    on stockin.serialno = stockout.serialno
group by stockin.serialno
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.