Hi , in below query I tried to joint some subqueries with “UNION ALL” operator to fetch some data in two columns (pack & warehouse) :
SELECT ft.leafCode
,ft.leafName
,ft.colorsCode
,ft.pack
,ft.warehouse
FROM (SELECT rs_leaves.leafCode
,rs_leaves.leafName
,rs_colors.colorsCode
,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM Else Null End), 0)
- COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum Else Null End), 0) As `pack`
,NULL AS `warehouse`
FROM sent_receive_tbl
INNER JOIN rs_leaves
ON sent_receive_tbl.leafID = rs_leaves.leafID
INNER JOIN rs_colors
ON sent_receive_tbl.colorsID = rs_colors.colorsID
Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
UNION ALL
SELECT tt.leafCode
,tt.leafName
,tt.colorsCode
,NULL AS `pack`
,tt.warehouse
FROM (SELECT st.leafCode
,st.leafName
,st.colorsCode
,NULL AS `pack`
,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
FROM (SELECT rs_leaves.leafCode
,rs_leaves.leafName
,rs_colors.colorsCode
,NULL As `INPUT`
,Sum(assign_details.assAmount) As `OUTPUT`
FROM assign_details
INNER JOIN rs_leaves
ON rs_leaves.leafID = assign_details.leafName
INNER JOIN rs_colors
ON rs_colors.colorsID = assign_details.leafColor
Group By rs_leaves.leafCode, rs_colors.colorsCode
UNION ALL
SELECT rs_leaves.leafCode
,rs_leaves.leafName
,rs_colors.colorsCode
,Sum(receipt_details.recAmount) As `INPUT`
,NULL As `OUTPUT`
FROM receipt_details
INNER JOIN rs_leaves
ON rs_leaves.leafID = receipt_details.leafName
INNER JOIN rs_colors
ON rs_colors.colorsID = receipt_details.leafColor
Group By rs_leaves.leafCode, rs_colors.colorsCode
) As st
Group By st.leafCode,st.leafName,st.colorsCode
) AS tt
) AS ft
Group By ft.leafCode
,ft.leafName
,ft.colorsCode;
In output I get NULL values as warehouse output but it is not valid :
±---------±---------±-----------±-----±----------+
| leafCode | leafName | colorsCode | pack | warehouse |
±---------±---------±-----------±-----±----------+
| A | Stone1 | 1014 | -5 | NULL |
| B | Stone2 | 1020 | 50 | NULL |
| B | Stone2 | 2000 | 345 | NULL |
| C | Stone3 | 1032 | 68 | NULL |
| F | Blur3 | 1014 | -6 | NULL |
| I | Wood3 | 1032 | 215 | NULL |
±---------±---------±-----------±-----±----------+
How can I take actual value for warehouse ?
These are some descriptions to confirm that warehouse shouldn`t drive NULL value :
mysql> select * from receipt_details;
±----------±------±---------±---------±----------±----------+
| detailsID | recID | leafName | leafCode | leafColor | recAmount |
±----------±------±---------±---------±----------±----------+
| 1 | 1 | 1 | A | 1 | 200 |
| 2 | 1 | 2 | B | 3 | 112 |
| 3 | 1 | 9 | I | 2 | 30 |
| 4 | 2 | 9 | I | 2 | 36 |
| 5 | 2 | 3 | C | 2 | 34 |
| 6 | 6 | 2 | B | 5 | 8 |
| 7 | 22 | 6 | F | 1 | 80 |
±----------±------±---------±---------±----------±----------+
mysql> select * from assign_details;
±----------±------±----------±---------±---------±----------±----------+
| detailsID | assID | projectID | leafName | leafCode | leafColor | assAmount |
±----------±------±----------±---------±---------±----------±----------+
| 1 | 1 | 1 | 1 | A | 1 | 20 |
| 2 | 1 | 1 | 2 | B | 3 | 8 |
| 3 | 2 | 2 | 3 | C | 2 | 4 |
| 4 | 2 | 2 | 9 | I | 2 | 6 |
| 5 | 2 | 1 | 6 | F | 1 | 30 |
| 6 | 3 | 3 | 2 | B | 5 | 12 |
| 7 | 3 | 1 | 2 | B | 3 | 5 |
| 8 | 1 | 1 | 6 | F | 1 | 20 |
| 9 | 3 | 1 | 1 | A | 1 | 15 |
±----------±------±----------±---------±---------±----------±----------+
thanks in advance