SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Problem with jointing via UNION , UNION ALL

    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 :
    Code:
    >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 ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :
    Code:
    >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:
    Code:
    >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 .

  6. #6
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ________________________________________________________________
    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 |
    +-----------+-------+----------+----------+-----------+-----------+

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 !?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    now run the UNION by itself, with the two subqueries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    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 .

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by omid020 View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mmmm,yes,there`s no difference ! I think that I should change something , but I don`t know what should I change exactly !

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 ?
    Code:
    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 ?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 !

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 .

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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 :
    Code:
    +-----------+-----------+----------+----------+-----------+-------------+------------+--------------+----------------+-----------------+--------+--------------+
    | 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 :
    Code MySQL:
    ,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 :
    Code MySQL:
    ,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 :
    Code MySQL:
    	,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 ?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •