merging queries with different columns

Question

I have two similar sql queries that only have different where clause filters to extract the appropriate admin names (point_of_contact, admin). I need to produce results that combines the results of both filtered queries and I was not sure of how to tackle that. The columns are the same except for point of contact and admin. I need the admin and point_of_contact to be in different columns. There is either a point of contact or admin but not both leaving one null. I have tried recursive sql, and case statements and Im having some trouble. I separated the queries into two simple queries below. (DB2 - not sure of the version)

SELECT  
                DP.DIM_PROJECT_ID, 
                DP.PROJECT_NAME, 
                DP.POINT_OF_CONTACT,
                DP.FIELD,
                DD.YEAR

            FROM FACT_Table FAT 
            RIGHT OUTER JOIN DIM_A AS DA on FAT.DIM_A_ID = DA.DIM_A_ID 
            INNER JOIN DIM_P DP on DA.DIM_P_ID = DP.DIM_P_ID
            INNER JOIN BRIDGE_USER BUP on BUP.BRIDGE_ID = DP.DIM_P_ID
            INNER JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
        WHERE DA.AWARD_CATEGORY <> 'N/A'   
        AND DD.YEAR = '2013'
        and (
                SELECT count(BUP_INNER.ADMIN_FLAG) FROM BRIDGE_USER BUP_INNER 
                INNER JOIN DIM_P DP_INNER on BUP_INNER.DIM_P_ID = DP_INNER.DIM_P_ID
                WHERE DP_INNER.DIM_P_ID = DP.DIM_P_ID
                    AND BUP_INNER.ADMIN_FLAG = 'Y'
                ) = 0
        GROUP BY DA.AWARD_TYPE_NAME, DA.AWARD_CATEGORY, DP.PROJECT_NAME, DP.POINT_OF_CONTACT, DD.YEAR

and

SELECT distinct
    DP.DIM_PROJECT_ID,
    DU.NAME_LAST CONCAT ', ' CONCAT t1.NAME_FIRST AS ADMIN,
    DP.PROJECT_NAME,
    DP.PROJECT_TITLE,
    DD.YEAR,
    DP.FIELD
FROM FACT_Table as FAT
INNER JOIN DIM_P DP ON FAT.DIM_P_ID = DP.DIM_P_ID
INNER JOIN BRIDGE_USER BUP on DP.DIM_P_ID = BUP.DIM_P_ID
INNER JOIN DIM_USER DU ON FAT.DIM_USER_ID = DU.DIM_USER_ID
INNER JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
INNER JOIN DIM_A DA ON FAT.DIM_A_ID = DA.DIM_A_ID

WHERE BUP.ADMIN_FLAG = 'Y'
and DD.YEAR = '2013'
and DA.AWARD_CATEGORY <> 'NA'

)
GROUP BY
    DP.DIM_P_ID,
    DU.NAME_LAST CONCAT ', ' CONCAT DU.NAME_FIRST,
    DP.PROJECT_NAME,
    DP.TITLE,
    DD.YEAR,
    DP.FIELD

score 1 · Answer 1 · answered Sep 03 '13 at 19:26

if both queries give the data you want (??) then why not have a name column and then a name type column.

In the first query put DP.POINT_OF_CONTACT as specialName. Add a column to this query "Point of Contact" as nameType.

In the second query change the "as ADMIN" to as specialName. Add a column to the query "Admin" as nameType.

You may have to do something about having a project_title in the first query but then you should be able to UNION the two sets of results. Then in your later queries that process the results you'll be able to manipulate on the nameType column.

Is this the direction you were going in? If not then may need more details in the question. Best Nick.

score 1 · Accepted Answer · answered Sep 03 '13 at 23:27

Set up extra columns that are null where they are not used

SELECT distinct 
         DP.DIM_PROJECT_ID, 
         DP.PROJECT_NAME,
         DP.PROJECT_TITLE,
         null as admin, 
         DP.POINT_OF_CONTACT,
         DP.FIELD,
         DD.YEAR

  FROM   FACT_Table   FAT 
  RIGHT OUTER 
  JOIN   DIM_A        DA    on FAT.DIM_A_ID = DA.DIM_A_ID 
  JOIN   DIM_P        DP    on DA.DIM_P_ID = DP.DIM_P_ID
  JOIN   BRIDGE_USER  BUP   on BUP.BRIDGE_ID = DP.DIM_P_ID
            JOIN DIM_DATE     DD    on DD.DATE_KEY = DA.A_START_DATE_ID
  WHERE DA.AWARD_CATEGORY <> 'N/A'   
    and DD.YEAR = '2013'
    and NOT EXISTS (
                     SELECT *
                       FROM BRIDGE_USER  BUP_INNER 
                       JOIN DIM_P        DP_INNER
                          on DP_INNER.DIM_P_ID = BUP_INNER.DIM_P_ID
                       WHERE DP_INNER.DIM_P_ID = DP.DIM_P_ID
                         AND BUP_INNER.ADMIN_FLAG = 'Y'
                   ) 
UNION ALL

SELECT distinct
    DP.DIM_PROJECT_ID,
    DP.PROJECT_NAME,
    DP.PROJECT_TITLE,
    DU.NAME_LAST CONCAT ', ' CONCAT t1.NAME_FIRST AS ADMIN,
    null as POINT_OF_CONTACT,
    DD.YEAR,
    DP.FIELD
  FROM FACT_Table as FAT
  JOIN DIM_P         DP   ON  FAT.DIM_P_ID = DP.DIM_P_ID
  JOIN BRIDGE_USER   BUP  on  BUP.DIM_P_ID = DP.DIM_P_ID
                          and BUP.ADMIN_FLAG = 'Y'
  JOIN DIM_USER      DU   ON  FAT.DIM_USER_ID = DU.DIM_USER_ID
  JOIN DIM_DATE      DD   on  DD.DATE_KEY = DA.A_START_DATE_ID
                          and DD.YEAR = '2013'
  JOIN DIM_A         DA   ON  DA.DIM_A_ID = FAT.DIM_A_ID
                          and DA.AWARD_CATEGORY <> 'NA'

NOT EXISTS should be more efficient since it will return an answer the moment one row is found satisfying the subquery. COUNT(*) on the other hand, will continue searching for all rows that satisfy the subquery.

I left out your GROUP BY clauses because I did not understand what you were trying to do with them since there are no aggregate functions involved.

You might also try replacing the `NOT EXISTS` logic with a `LEFT EXCEPTION JOIN` to the subquery. You could check Visual Explain to see which is more likely to perform better. — WarrenT, Sep 04 '13 at 17:52

merging queries with different columns

2 Answers2

Linked