UNION ALL doesn`t return all available data

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

i don’t think anything here is substantially different from the last time you posted this problem

the debugging technique i would use is to start at the innermost subquery and run it completely by itself, verifying that it does produce the desired results

then build up, one step at a time

i am afraid that for anyone else who does not have access to your data – or to an understanding of what you’re doing – this problem is too complex to give an answer on a forum like this

I have just simplified it to be easy to analyze .
Innermost subquery is working fine , I can gain warehouse values truly :

±---------±---------±-----------±-----±----------+
| leafCode | leafName | colorsCode | pack | warehouse |
±---------±---------±-----------±-----±----------+
| A | Stone1 | 1014 | NULL | 165 |
| B | Stone2 | 1020 | NULL | -4 |
| B | Stone2 | 2000 | NULL | 99 |
| C | Stone3 | 1032 | NULL | 30 |
| F | Blur3 | 1014 | NULL | 30 |
| I | Wood3 | 1032 | NULL | 60 |
±---------±---------±-----------±-----±----------+

So 50% is done correctly . Now I need to gain pack values simultaneously ;
Like warehouse , pack is related to such data as leafCode , leafName , colorsCode ;
So both columns are related to similar parameters and occasionally they are related to common data .

i am afraid that for anyone else who does not have access to your data – or to an understanding of what you’re doing

Maybe I`m going wrong !In simple description :
I have 3 tabels : for example (t1 , t2 , t3) .
warehouse is obtained from t1 and t2 ; pack is obtained from t3 ; now I need to join these tables to have warehouse and pack simultaneously ; Is the method I choosed to joining these tables correctly ? Is there any alternative solution for it ?

In upper stage I gain correct output too :

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

result :

±---------±---------±-----------±-----±----------+
| 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 |
| A | Stone1 | 1014 | NULL | 165 |
| B | Stone2 | 1020 | NULL | -4 |
| B | Stone2 | 2000 | NULL | 99 |
| C | Stone3 | 1032 | NULL | 30 |
| F | Blur3 | 1014 | NULL | 30 |
| I | Wood3 | 1032 | NULL | 60 |
±---------±---------±-----------±-----±----------+

sorry, i don’t understand your data

:blush: Ok , which data do you need ? or which lines should be described more?
Could you please guide me about ambiguous information ?
I tried to describe clearly what output I need , I hope my descriptions would be realizable for you because english is not my native language :cool:
Ah ! this forum is my final trust to solve this problem .

well, we can start here –

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

what does this produce and is it correct?

Above query displays INPUT and OUTPUT quantities of warehouse . In upper step this formula is used to calculate current inventory of warehouse :

,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As warehouse

Above query result is correct :

±---------±---------±-----------±------±-------+
| leafCode | leafName | colorsCode | INPUT | OUTPUT |
±---------±---------±-----------±------±-------+
| A | Stone1 | 1014 | NULL | 35 |
| B | Stone2 | 1020 | NULL | 12 |
| B | Stone2 | 2000 | NULL | 13 |
| C | Stone3 | 1032 | NULL | 4 |
| F | Blur3 | 1014 | NULL | 50 |
| I | Wood3 | 1032 | NULL | 6 |
| A | Stone1 | 1014 | 200 | NULL |
| B | Stone2 | 1020 | 8 | NULL |
| B | Stone2 | 2000 | 112 | NULL |
| C | Stone3 | 1032 | 34 | NULL |
| F | Blur3 | 1014 | 80 | NULL |
| I | Wood3 | 1032 | 66 | NULL |
±---------±---------±-----------±------±-------+

Also in upper step , I mean this code :

SELECT  st.leafCode
              ,st.leafName
              ,st.colorsCode
              ,NULL AS `pack`
              ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
              FROM (
[COLOR="Silver"]        ---here is subquery---[/COLOR]
                   ) As st
                     GROUP BY st.leafCode,st.leafName,st.colorsCode

drived result is correct :

±---------±---------±-----------±-----±----------+
| leafCode | leafName | colorsCode | pack | warehouse |
±---------±---------±-----------±-----±----------+
| A | Stone1 | 1014 | NULL | 165 |
| B | Stone2 | 1020 | NULL | -4 |
| B | Stone2 | 2000 | NULL | 99 |
| C | Stone3 | 1032 | NULL | 30 |
| F | Blur3 | 1014 | NULL | 30 |
| I | Wood3 | 1032 | NULL | 60 |
±---------±---------±-----------±-----±----------+

P.S : Red numbers tell the story .

okay, so this part is correct, right?

then which part is wrong?

Yes , that`s correct .
In upper step drived result is correct too :

SELECT   tt.leafCode
             ,tt.leafName
             ,tt.colorsCode
             ,NULL AS `pack`
             ,tt.warehouse
            FROM (
   [COLOR="Silver"]   ---this is subquery---[/COLOR]
                ) AS tt; 

After using UNION ALL drived result is correct again :

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 (
      [COLOR="Silver"]---here is subquery---[/COLOR]
               ) AS tt;

±---------±---------±-----------±-----±----------+
| 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 |
| A | Stone1 | 1014 | NULL | 165 |
| B | Stone2 | 1020 | NULL | -4 |
| B | Stone2 | 2000 | NULL | 99 |
| C | Stone3 | 1032 | NULL | 30 |
| F | Blur3 | 1014 | NULL | 30 |
| I | Wood3 | 1032 | NULL | 60 |
±---------±---------±-----------±-----±----------+

In above step I tried to fetch pack column values from sent_receive_tbl table .
But in final step when I try to have warehouse and pack values simultaneously in one individual table with unique data (I mean every unique product has just one row in table) , the result will be wrong ! because I get NULL values for warehouse :

SELECT   ft.leafCode
        ,ft.leafName
        ,ft.colorsCode
        ,ft.pack
        ,ft.warehouse
FROM (
      [COLOR="Silver"]---here are subqueries---[/COLOR]
       ) AS ft
      GROUP BY ft.leafCode
      ,ft.leafName
      ,ft.colorsCode;

±---------±---------±-----------±-----±----------+
| 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 |
±---------±---------±-----------±-----±----------+

you lost me again

Could you please tell me in which section ? Is it required to place tables structures here ?
My favorite result should be :
±---------±---------±-----------±-----±----------+
| leafCode | leafName | colorsCode | pack | warehouse |

±---------±---------±-----------±-----±----------+
| A | Stone1 | 1014 | -5 | 165 |
| B | Stone2 | 1020 | 50 | -4 |
| B | Stone2 | 2000 | 345 | 99 |
| C | Stone3 | 1032 | 68 | 30 |
| F | Blur3 | 1014 | -6 | 30 |
| I | Wood3 | 1032 | 215 | 60 |
±---------±---------±-----------±-----±----------+

But now as I described warehouse result for all records is NULL !

what does this produce –

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

Ok , I used above query to find availabe inventory into 7th workstation ; I name it pack because that station is (packing workstation) .
sent_receive_tbl table structure is :

±----------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±------------±-----±----±--------±---------------+
| srID | int(11) | NO | PRI | NULL | auto_increment |
| checkNumber | varchar(20) | NO | | NULL | |
| leafID | int(11) | NO | | NULL | |
| colorsID | int(11) | YES | | NULL | |
| stationID | int(11) | NO | | NULL | |
| receivedNum | double | NO | | 0 | |
| sentNum | double | NO | | 0 | |
| transactionDate | datetime | YES | | NULL | |
| transactionTime | time | NO | | NULL | |
| srComment | text | YES | | NULL | |
±----------------±------------±-----±----±--------±---------------+

and does the query in post #12 produce the correct results?

Yes , it produces the correct results .

could you show them please?

For query in post #12 :

±---------±---------±-----------±-----±----------+
| 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 |
±---------±---------±-----------±-----±----------+

okay, i see what you are doing wrong (finally)

you have 3 columns in your outer GROUP BY clause, and 5 non-aggregate columns in your SELECT clause

SELECT   [COLOR="red"]ft.leafCode
        ,ft.leafName
        ,ft.colorsCode
        ,ft.pack
        ,ft.warehouse[/COLOR]
FROM (
   [COLOR="Silver"]   ---here are subqueries---[/COLOR]  
     ) AS ft
      GROUP BY [COLOR="blue"]ft.leafCode
      ,ft.leafName
      ,ft.colorsCode[/COLOR];

please go back and re-read the documentation about hidden columns, and then see if you can identify which of these columns are hidden

In documentation there is one sentence that is not clear to me :

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part.

What`s the purpose of “same values” ? I mean “same values” in which aspects ?
Could you please describe it with one simple example ?

here’s a simple example

the data produced by your UNION query consists of the following rows from the first SELECT

leafCode  leafName  colorsCode  pack  warehouse
   A      Stone1       1014     NULL     165
   B      Stone2       1020     NULL      -4
   B      Stone2       2000     NULL      99
   C      Stone3       1032     NULL      30
   F      Blur3        1014     NULL      30
   I      Wood3        1032     NULL      60

as well as the following rows from the second SELECT

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 

now let’s rearrange them and put them into groups

leafCode  leafName  colorsCode  pack  warehouse
   A      Stone1       1014       -5    NULL 
   A      Stone1       1014     NULL     165

   B      Stone2       1020       50    NULL 
   B      Stone2       1020     NULL      -4

   B      Stone2       2000      345    NULL 
   B      Stone2       2000     NULL      99

   C      Stone3       1032       68    NULL 
   C      Stone3       1032     NULL      30

   F      Blur3        1014       -6    NULL 
   F      Blur3        1014     NULL      30

   I      Wood3        1032      215    NULL
   I      Wood3        1032     NULL      60

these are the groups as defined by the GROUP BY columns – leafCode, leafName, colorsCode

let’s look within the first group

leafCode  leafName  colorsCode  pack  warehouse
   A      Stone1       1014       -5    NULL 
   A      Stone1       1014     NULL     165

here you can see that the values of the pack and warehouse columns are ~not~ the “same values” for all rows in the group

consequently mysql is free to pick whichever value it wants for thes

the solution is to apply an aggregate function to these columns

:slight_smile: