Problem with jointing via UNION , UNION ALL

[omid020]

Hi,

I wrote a query with multiple subqueries , and I tried to join them with UNION ALL to get my favorite result ; every subquery works fine as individual module but when I combine them with UNION ALL I get strange result .

This is a short story about main query :
I have some projects , each project has some products that identified with (leafCode,leafName,colorsCode) in DB .Also each project has some other elements like (orderAmount) that identify related project order quantity and (orderQuota) that is ration of product quantity that is not related to that project; Produced products could be placed in some locations as (polish,pack,warehouse,sent) .
Now I need to calculate progress of every defined project with using above fields .

This is code details :

>SELECT  ft.projectName
>        ,ft.leafCode
>        ,ft.leafName
>        ,ft.colorsCode
>        ,ft.polish
>        ,ft.pack
>        ,COALESCE(ft.warehouse, 0) AS `warehouse`
>        ,ft.sent
>        ,COALESCE(ft.orderQuota, 0) AS `orderQuota`
>        ,(COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0)) - (COALESCE(ft.orderQuota, 0)) AS `available`
>        ,COALESCE(ft.orderAmount, 0) AS `orderAmount`
>        ,COALESCE(COALESCE(ft.orderAmount, 0)) - ((COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0) + COALESCE(ft.sent, 0)) - (COALESCE(ft.orderQuota, 0))) AS `projectRemained`
>        ,ROUND((((((COALESCE(ft.polish, 0) + COALESCE(ft.pack, 0) + COALESCE(ft.warehouse, 0) + COALESCE(ft.sent, 0)) - COALESCE(ft.orderQuota)) / COALESCE(ft.orderAmount, 0))) * 100), 2) AS `Progress(%)`
>FROM (
>SELECT projects.projectName
>  		,rs_leaves.leafCode
>  		,rs_leaves.leafName
>  		,rs_colors.colorsCode
>  		,NULL AS `polish`
>		,NULL AS `pack`
>		,NULL AS `warehouse`
>  		,SUM(Distinct assign_details.assAmount) AS `sent`
>  		,projects_details.orderQuota
>		,projects_details.orderAmount
>FROM assign_details
>INNER JOIN rs_leaves
>ON assign_details.leafName = rs_leaves.leafID
>INNER JOIN rs_colors
>ON assign_details.leafColor = rs_colors.colorsID
>INNER JOIN projects_details
>ON assign_details.projectID = projects_details.projectID
>INNER JOIN projects
>ON projects_details.projectID = projects.projectID
>Group By rs_leaves.leafCode,
>  rs_leaves.leafName,
>  rs_colors.colorsCode,
>  projects_details.projectID
>UNION ALL
>SELECT	projects.projectName
>		,rs_leaves.leafCode
>		,rs_leaves.leafName
>		,rs_colors.colorsCode
>		,NULL AS `polish`
>		,NULL AS `pack`
>		,NULL AS `warehouse`
>		,NULL AS `sent`
>		,projects_details.orderQuota
>		,projects_details.orderAmount
>FROM projects_details
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = projects_details.leafName
>INNER JOIN rs_colors
>ON rs_colors.colorsID = projects_details.leafColor
>INNER JOIN projects
>ON projects.projectID = projects_details.projectID
>INNER JOIN assign_details
>ON projects_details.projectID = assign_details.projectID
>Group By projects_details.projectID, rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
>UNION ALL
>SELECT	NULL AS `projectName`
>		,rs_leaves.leafCode
>		,rs_leaves.leafName
>		,rs_colors.colorsCode
>		,COALESCE(Sum(patine_tbl.receivedNum), 0) - COALESCE(Sum(Case
>    	When sent_receive_tbl.stationID = '6' Then sent_receive_tbl.sentNum
>    	Else Null End), 0) As `ploish`
>    	,NULL AS `pack`
>    	,NULL AS `warehouse`
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>		,NULL AS `orderAmount`
>FROM patine_tbl
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = patine_tbl.leafID
>INNER JOIN rs_colors
>ON rs_colors.colorsID = patine_tbl.colorsID
>LEFT JOIN sent_receive_tbl
>ON sent_receive_tbl.leafID = rs_leaves.leafID
>AND sent_receive_tbl.colorsID = rs_colors.colorsID
>Group By rs_leaves.leafCode,
>  rs_leaves.leafName,
>  rs_colors.colorsCode
>UNION ALL
>SELECT	NULL AS `projectName`
>		,rs_leaves.leafCode
>		,rs_leaves.leafName
>		,rs_colors.colorsCode
>		,NULL AS `polish`
>		,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`
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>		,NULL AS `orderAmount`
>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	NULL AS `projectNmae`
>		,NULL AS `leafCode`
>		,NULL AS `leafName`
>		,NULL AS `colorsCode`
>		,NULL AS `polish`
>		,NULL AS `pack`
>    	,tt.warehouse
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>				FROM (SELECT st.leafCode,
>  									st.colorsCode,
>  									COALESCE(Sum(st.INPUT), 0) As `INPUT`,
>  									COALESCE(Sum(st.OUTPUT), 0) As OUTPUT,
>  									COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
>										FROM (SELECT rs_leaves.leafCode
>										,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
>    									UNION All
>    									SELECT rs_leaves.leafCode
>    									,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
>										) As st
>							Group By leafCode,colorsCode
>					    	) AS tt
>     ) AS ft
>Group By ft.projectName,ft.leafCode
>,ft.leafName
>,ft.colorsCode;

And this screenshot is the result :


Result has some issues that I was not successful to resolve them :
1 - Why SQL return NULL values in first row (except warehouse) and why warehouse is NULL in other rows ?
2 - Why SQL returns NULL value for projectName in some fields , regard to available data in DB I know that every product in every place has a related project so I shouldn`t have NULL value as projectName ?
3- Why when I remove "Distinct" statement after "SUM" , SQL returns wrong values for (sent) field ?
4- And catastrophe , most important fields like warehouse,polish,pack, orderAmount,orderQuota have wrong output .

I need to view all available projects and their unique details through all related data to those projects for calculating project progress ;
Could you please help me ?

[r937]

this query is ~far~ too complicated for anyone to help you with, sorry

the reason you're getting the results that you are getting is because that is exactly what your query is asking for

for example, the NULLs are probably because of the SELECT NULL subqueries...

we don't know why your query is producing the wrong output, because we don't know what the right output looks like

my advice is for you to start with the deepest subqueries, and run them by themselves, and understand why they produce what they produce, before you UNION those subqueries or nest those subqueries inside other queries...

[omid020]

Thanks Rudy , I`m concern about the method I`m choosed to join tables and subqueries . I`m dummy with UNION and its treatments with "Group By" statement ; I`m just sure that every subquery works fine individually but I don`t know their syntax is suitable for working with other subqueries too ! But I`ll try to reanalyze them with deepest subquery and I hope to find prodigious changes .

Regards

[SpacePhoenix]

omid020, can you post a "CREATE TABLE" and sample data for each table and explain what information you are trying to extract from the query?

[omid020]

I started with deepest subquery but I couldn`t find specific reason for obtained result in first step !

When I write my code like this , "projectName" will be NULL :

>SELECT	 tt.projectName
>	,tt.leafCode
>	,tt.leafName
>	,tt.colorsCode
>	,NULL AS `polish`
>	,NULL AS `pack`
>    	,tt.warehouse
>   	,NULL AS `sent`
>   	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>             FROM (SELECT st.projectName,
>                    st.leafCode,
>                    st.leafName,
>  		    st.colorsCode,
>  		    COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
>		    FROM (SELECT NULL AS `projectName`
>                          ,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
>                    UNION ALL
>                    	  SELECT projects.projectName
>                          ,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
>                          INNER JOIN projects
>                          ON projects.projectID = assign_details.projectID
>			  Group By rs_leaves.leafCode, rs_colors.colorsCode
>			 ) As st
>	     Group By st.leafCode,st.colorsCode
>              ) AS tt;

+-------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
| projectName | leafCode | leafName | colorsCode | polish | pack | warehouse | sent | orderQuota | orderAmount |
+-------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
| NULL | A | Stone1 | 1014 | NULL | NULL | 165 | NULL | NULL | NULL |
| NULL | B | Stone2 | 1020 | NULL | NULL | -4 | NULL | NULL | NULL |
| NULL | B | Stone2 | 2000 | NULL | NULL | 99 | NULL | NULL | NULL |
| NULL | C | Stone3 | 1032 | NULL | NULL | 30 | NULL | NULL | NULL |
| NULL | F | Blur3 | 1014 | NULL | NULL | 30 | NULL | NULL | NULL |
| NULL | I | Wood3 | 1032 | NULL | NULL | 60 | NULL | NULL | NULL |
+-------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
6 rows in set (0.14 sec)

But when I write my code like this , projectName will be right:

>SELECT	 tt.projectName
>	,tt.leafCode
>	,tt.leafName
>	,tt.colorsCode
>	,NULL AS `polish`
>	,NULL AS `pack`
>    	,tt.warehouse
>   	,NULL AS `sent`
>   	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>             FROM (SELECT st.projectName,
>                    st.leafCode,
>                    st.leafName,
>  		    st.colorsCode,
>  		    COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
>		    FROM (SELECT projects.projectName
>                          ,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
>                          INNER JOIN projects
>                          ON projects.projectID = assign_details.projectID
>			  Group By rs_leaves.leafCode, rs_colors.colorsCode
>			UNION ALL
>			  SELECT NULL AS `projectName`
>                          ,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.colorsCode
>              ) AS tt;

+----------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
| projectName | leafCode | leafName | colorsCode | polish | pack | warehouse | sent | orderQuota | orderAmount |
+----------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
| North Building | A | Stone1 | 1014 | NULL | NULL | 165 | NULL | NULL | NULL |
| East Building | B | Stone2 | 1020 | NULL | NULL | -4 | NULL | NULL | NULL |
| North Building | B | Stone2 | 2000 | NULL | NULL | 99 | NULL | NULL | NULL |
| West Building | C | Stone3 | 1032 | NULL | NULL | 30 | NULL | NULL | NULL |
| North Building | F | Blur3 | 1014 | NULL | NULL | 30 | NULL | NULL | NULL |
| West Building | I | Wood3 | 1032 | NULL | NULL | 60 | NULL | NULL | NULL |
+----------------+----------+----------+------------+--------+------+-----------+------+------------+-------------+
6 rows in set (0.00 sec)

Why changing the subqueries priority before and after UNION ALL cause to different result ?

These are some hints about tables I used above :

mysql> describe projects;
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+----------------+
| projectID | int(11) | NO | PRI | NULL | auto_increment |
| projectName | varchar(255) | NO | | NULL | |
| orderNumber | varchar(50) | NO | | NULL | |
| projectPri | tinyint(4) | YES | | NULL | |
| projectProduction | enum('Y','N') | NO | | N | |
| projectSent | enum('Y','N') | NO | | N | |
+-------------------+---------------+------+-----+---------+----------------+

mysql> describe rs_leaves;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| leafID | int(11) | NO | PRI | NULL | auto_increment |
| leafCode | varchar(1) | NO | | NULL | |
| leafName | varchar(255) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+

mysql> describe rs_colors;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| colorsID | int(11) | NO | PRI | NULL | auto_increment |
| colorsCode | varchar(25) | NO | | NULL | |
| colorsPicture | varchar(50) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+

mysql> describe assign_details;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| detailsID | int(11) | NO | PRI | NULL | auto_increment |
| assID | int(11) | NO | PRI | 0 | |
| projectID | int(11) | NO | | NULL | |
| leafName | int(11) | NO | | NULL | |
| leafCode | varchar(5) | NO | | NULL | |
| leafColor | int(11) | NO | | NULL | |
| assAmount | double | NO | | NULL | |
| assComment | text | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+

mysql> describe receipt_details;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| detailsID | int(11) | NO | PRI | NULL | auto_increment |
| recID | int(11) | NO | PRI | 0 | |
| leafName | int(11) | NO | | NULL | |
| leafCode | varchar(5) | NO | | NULL | |
| leafColor | int(11) | NO | | NULL | |
| recAmount | double | NO | | NULL | |
| recComment | text | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+

In next post I will place tables content .

[omid020]

________________________________________________________________

mysql> select * from projects;
+-----------+----------------+-------------+------------+-------------------+-------------+
| projectID | projectName | orderNumber | projectPri | projectProduction | projectSent |
+-----------+----------------+-------------+------------+-------------------+-------------+
| 1 | North Building | 88-27 | 1 | N | N |
| 2 | West Building | 88-28 | 2 | N | N |
| 3 | East Building | 88-29 | 3 | Y | N |
+-----------+----------------+-------------+------------+-------------------+-------------+

mysql> select * from rs_leaves;
+--------+----------+----------+
| leafID | leafCode | leafName |
+--------+----------+----------+
| 1 | A | Stone1 |
| 2 | B | Stone2 |
| 3 | C | Stone3 |
| 4 | D | Blur1 |
| 5 | E | Blur2 |
| 6 | F | Blur3 |
| 7 | G | Wood1 |
| 8 | H | Wood2 |
| 9 | I | Wood3 |
| 10 | J | Antique1 |
| 11 | K | Antique2 |
| 12 | L | Antique3 |
| 13 | M | Puzzle1 |
| 14 | N | Puzzle2 |
| 15 | O | Nail1 |
| 16 | P | Nail2 |
+--------+----------+----------+

mysql> select * from rs_colors;
+----------+------------+---------------+
| colorsID | colorsCode | colorsPicture |
+----------+------------+---------------+
| 1 | 1014 | 1014.png |
| 2 | 1032 | 1032.png |
| 3 | 2000 | 2000.png |
| 4 | 2001 | 2001.png |
| 5 | 1020 | 1020.png |
| 6 | 6028 | 6028.png |
+----------+------------+---------------+

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

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

[r937]

you've marked two subqueries in red and blue, and you show 6 rows when the red subquery comes first in the UNION, and 6 different rows when the blue subquery comes first in the UNION

run the red subquery by itself, then run the blue subquery by itself

[omid020]

you've marked two subqueries in red and blue, and you show 6 rows when the red subquery comes first in the UNION, and 6 different rows when the blue subquery comes first in the UNION

run the red subquery by itself, then run the blue subquery by itself

Thanks for your reply ;
Blue subquery by itself :

+----------------+----------+----------+------------+-------+--------+
| projectName | leafCode | leafName | colorsCode | INPUT | OUTPUT |
+----------------+----------+----------+------------+-------+--------+
| North Building | A | Stone1 | 1014 | NULL | 35 |
| East Building | B | Stone2 | 1020 | NULL | 12 |
| North Building | B | Stone2 | 2000 | NULL | 13 |
| West Building | C | Stone3 | 1032 | NULL | 4 |
| North Building | F | Blur3 | 1014 | NULL | 50 |
| West Building | I | Wood3 | 1032 | NULL | 6 |
+----------------+----------+----------+------------+-------+--------+

Red subquery by itself :

+-------------+----------+----------+------------+-------+--------+
| projectName | leafCode | leafName | colorsCode | INPUT | OUTPUT |
+-------------+----------+----------+------------+-------+--------+
| NULL | A | Stone1 | 1014 | 200 | NULL |
| NULL | B | Stone2 | 1020 | 8 | NULL |
| NULL | B | Stone2 | 2000 | 112 | NULL |
| NULL | C | Stone3 | 1032 | 34 | NULL |
| NULL | F | Blur3 | 1014 | 80 | NULL |
| NULL | I | Wood3 | 1032 | 66 | NULL |
+-------------+----------+----------+------------+-------+--------+

What should I do now !?

[r937]

now run the UNION by itself, with the two subqueries

[omid020]

SELECT NULL AS `projectName`
          ,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
           UNION
SELECT projects.projectName
          ,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
          INNER JOIN projects
          ON projects.projectID = assign_details.projectID
	  Group By rs_leaves.leafCode, rs_colors.colorsCode

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

Is that right as your desire ? I used UNION not UNION ALL .

[r937]

I used UNION not UNION ALL

there is no difference in this particular example, because there are no duplicate rows -- UNION ALL would be better than UNION because UNION ALL does not need to sort the rows in order to find duplicates, of which there aren't any anyway

next step: put this UNION query into the next higher query --

SELECT st.projectName
     , st.leafCode
     , st.leafName
     , st.colorsCode
     , COALESCE(SUM(st.INPUT), 0) - 
       COALESCE(SUM(st.OUTPUT), 0) AS `warehouse`
  FROM ( SELECT NULL AS `projectName`
              , 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
         UNION ALL
         SELECT projects.projectName
              , 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
         INNER 
           JOIN projects
             ON projects.projectID = assign_details.projectID
         GROUP 
             BY rs_leaves.leafCode
              , rs_colors.colorsCode
       ) AS st
GROUP 
    BY st.leafCode
     , st.colorsCode

now here comes the interesting part -- compare the results of this with the results you get when you switch the order of the UNION subqueries

i'm pretty sure you will find no difference

[omid020]

Mmmm,yes,there`s no difference ! I think that I should change something , but I don`t know what should I change exactly !

[r937]

have you analyzed the results produced by the query in post #11?

i mean, other than confirming that the order of the subqueries makes no difference?

are these results correct? the correct data?

[omid020]

have you analyzed the results produced by the query in post #11?

i mean, other than confirming that the order of the subqueries makes no difference?

are these results correct? the correct data?

All things is like before ! this is result produced by the query in post #11 :

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

Here project names are NULL and it`s wrong result .

When I change subqueries arrangement, project names will not be NULL and that is correct result . Now is it usual ?

SELECT st.projectName
     , st.leafCode
     , st.leafName
     , st.colorsCode
     , COALESCE(SUM(st.INPUT), 0) - 
       COALESCE(SUM(st.OUTPUT), 0) AS `warehouse`
  FROM ( 
         SELECT projects.projectName
              , 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
         INNER 
           JOIN projects
             ON projects.projectID = assign_details.projectID
         GROUP 
             BY rs_leaves.leafCode
              , rs_colors.colorsCode
UNION ALL
	SELECT NULL AS `projectName`
              , 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.colorsCode

And result :
+----------------+----------+----------+------------+-----------+
| projectName | leafCode | leafName | colorsCode | warehouse |
+----------------+----------+----------+------------+-----------+
| North Building | A | Stone1 | 1014 | 165 |
| East Building | B | Stone2 | 1020 | -4 |
| North Building | B | Stone2 | 2000 | 99 |
| West Building | C | Stone3 | 1032 | 30 |
| North Building | F | Blur3 | 1014 | 30 |
| West Building | I | Wood3 | 1032 | 60 |
+----------------+----------+----------+------------+-----------+

I mean when we change orders of subqueries , changes in results are predictable or we shouldn`t have any change in results ?

[r937]

okay, i think i understand what's happening now

you have a defective GROUP BY query

may i ask you to please describe in words what this query is doing --

SELECT st.projectName
     , st.leafCode
     , st.leafName
     , st.colorsCode
     , COALESCE(SUM(st.INPUT), 0) - 
       COALESCE(SUM(st.OUTPUT), 0) AS `warehouse`
  FROM ( your UNION query ) AS st
GROUP 
    BY st.leafCode
     , st.colorsCode

this query returns one row for each distinct combination of leafcode and colorscode

what is the purpose of choosing projectName in this query?

sometimes the project name is valid, and sometimes it is NULL -- and the sequence of rows in the UNION is unfortunately causing mysql to choose either the NULL or the project name

for more information on what's happening here, please see GROUP BY and HAVING with Hidden Columns

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. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

so the value for st.projectName is indeterminate as explained by the documentation

why are you assigning NULL to the project name in the receipt_details subquery? why aren't you joining to the projects table the same way as the assign_details subquery?

that's the source of your problem

[omid020]

You mean I should add st.projectName to "Group By" statement ? I did it yesterday but as I remember the result for "warehouse" will be wrong with grouping projectName !

[r937]

why are you assigning NULL to the project name in the receipt_details subquery? why aren't you joining to the projects table the same way as the assign_details subquery?

[omid020]

why are you assigning NULL to the project name in the receipt_details subquery? why aren't you joining to the projects table the same way as the assign_details subquery?

I can describe it as my real world sample . receipt_details determines (INPUTS) of warehouse and we don`t know those (INPUTS) for which project will be used ? Maybe never use them for an specific project ! But assign_details determines (OUTPUTS) of warehouse . We don`t let our warehouse have OUTPUT unless we know where the OUTPUT will be sent , so always we know OUTPUTS are belonged to which project and every OUTPUT should have an specific project .

[r937]

okay, that makes sense

can you please now explain why you are showing a project name at all?

i mean, if inputs have no projects, and you are subtracting outputs from inputs, what difference does it make which projects are involved?

also, isn't it possible that for a particular type of product, you could send output to more than one project?

so if the purpose of the inputs-minus-outputs calculation is just a warehouse inventory, do you really need the project name?

[omid020]

i mean, if inputs have no projects, and you are subtracting outputs from inputs, what difference does it make which projects are involved?

Because I need to calculate every detailed project progress (in percent) finally .

also, isn't it possible that for a particular type of product, you could send output to more than one project?

Yes , because every product could be sent to more than one project , I prefer to don`t allot any dedicated project for INPUTS .

so if the purpose of the inputs-minus-outputs calculation is just a warehouse inventory, do you really need the project name?

Now I think that I should ignore project name in this step ! Maybe I should gain project name from superior subqueries !?

Let me describe more about my query :
Every project has some details that I`ve placed them in (projects_details) tabel :

mysql> describe projects_details;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| detailsID | int(11) | NO | PRI | NULL | auto_increment |
| projectID | int(11) | NO | PRI | 0 | |
| leafName | int(11) | NO | | NULL | |
| leafCode | varchar(5) | NO | | NULL | |
| leafColor | int(11) | NO | | NULL | |
| orderAmount | double | NO | | NULL | |
| orderQuota | double | NO | | NULL | |
| priorityDone | tinyint(4) | YES | | NULL | |
| similarProject | enum('Y','N') | NO | | N | |
| projectFinished | enum('Y','N') | NO | | N | |
| noNeed | enum('Y','N') | NO | | N | |
| noNeedNumber | int(4) | YES | | 0 | |
+-----------------+---------------+------+-----+---------+----------------+

I used and need only these fields from above table in my query :
projectID,leafName,leafCode,leafColor,orderAmount,orderQuota
I need orderAmount and orderQuota for calculating projects progress .
This is projects_details content :

+-----------+-----------+----------+----------+-----------+-------------+------------+--------------+----------------+-----------------+--------+--------------+
| detailsID | projectID | leafName | leafCode | leafColor | orderAmount | orderQuota | priorityDone | similarProject | projectFinished | noNeed | noNeedNumber |
+-----------+-----------+----------+----------+-----------+-------------+------------+--------------+----------------+-----------------+--------+--------------+
|         1 |         1 |        1 | A        |         1 |         168 |          0 |            0 | N              | N               | N      |            0 |
|         2 |         1 |        2 | B        |         3 |         345 |          6 |            0 | N              | N               | N      |            0 |
|         3 |         1 |        6 | F        |         1 |          56 |          5 |            0 | Y              | N               | N      |            0 |
|         4 |         2 |        9 | I        |         2 |         214 |          0 |            5 | N              | N               | N      |            0 |
|         5 |         2 |        3 | C        |         2 |          68 |          8 |            3 | N              | N               | N      |            0 |
|         6 |         3 |        2 | B        |         5 |          89 |         19 |            1 | N              | N               | N      |            0 |
|         7 |         3 |        1 | A        |         1 |          79 |         16 |            2 | Y              | N               | N      |            0 |
|         8 |         3 |       14 | N        |         6 |          56 |          0 |            3 | N              | N               | N      |            0 |
+-----------+-----------+----------+----------+-----------+-------------+------------+--------------+----------------+-----------------+--------+--------------+

Now I need one query that can give me overview of these parameters :
1 - projectName ---> Name of every available project .
2 - leafCode ---> Code of product for every product that is assigned to project
3 - leafName ---> Name of product for every product that is assigned to project
4 - leafColor ---> Color of product for every product that is assigned to project
5 - polish ---> Available quantity of product in polish workstation .
I used this code to calculate polish value :

,COALESCE(Sum(patine_tbl.receivedNum), 0) - COALESCE(Sum(Case
    	When sent_receive_tbl.stationID = '6' Then sent_receive_tbl.sentNum
    	Else Null End), 0) As `ploish`

6 - pack ---> Available quantity of product in packing workstation .
I used this code to calculate pack value :

,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`

7 - warehouse ---> related code is described in previous threads .
8 - sent ---> Is quantity of products that have been sent to related project .
I used this code to obtain it :

	,SUM(Distinct assign_details.assAmount) AS `sent`

, here again I used assign_details table .
9 - orderQuota ---> is ration of product quantity that is not related to that project and belong to other projects - this could be obtained from projects_details table .
10 - orderAmount ---> Quantity of order for related project and could be obtained from projects_details

Regard to above comment and query that I`ve placed in post #1 , do I`ve selected suitable subqueries to combine with each other toward to gain above parameters for each detailed project ?

[r937]

please do not take this the wrong way, but i don't think i can help you any further with this project

it is getting ~way~ too complicated for a forum discussion

i hope that i have helped you in debugging your SQL, and that you have learned something along the way

[omid020]

Ok , that is a complicated project ; I hope that they would be just some simple bugs because I`m fear that all problems are drived from missing some important philosophies like "GROUP BY and HAVING with Hidden Columns" that can astound me for long days .

Again thanks for your good support .

Regards
Omid