CODEWITHSUNDEEP
mysqljoinunion
mrickan
mrickan

Reputation: 13

Joining Result Sets from Two Unions in MySQL

I have a third party application that generates data into a number of separate tables based on the release. The data format remains consistent, so unions can be performed on the different types of tables.

I would like to include fields from the two unions in one resulting dataset.

The first union is an list of equipment used and is produced using the following statement:

SELECT *
  FROM (SELECT * FROM PMT_EQUIPMENT_USED_OLD
    UNION
  SELECT * FROM PMT_EQUIPMENT_USED_X
    ) AS U
WHERE U.GRID_EQUIPMENT_ID IS NOT NULL AND U.APP_STATUS <> 'CANCELLED' ORDER  BY U.APP_NUMBER, U.ROW

There can be multiple table entries for each piece of equipment in this table.

Here is the table structure:

Field,Type,Null,Key,Default
APP_UID,varchar(32),NO,PRI,null
APP_NUMBER,int(11),NO,,null
APP_STATUS,varchar(10),NO,,null
ROW,int(11),NO,PRI,null
GRID_EQUIPMENT_ID,int(11),YES,,null
GRID_EQUIPMENT_ID_LABEL,varchar(64),YES,,null

The second union is an inventory report produced using the following statement: 

SELECT *
  FROM (SELECT * FROM PMT_RPT_OLD
    UNION
  SELECT * FROM PMT_RPT_X
    ) AS A
ORDER BY A.APP_NUMBER

Here is the table structure:

Field,Type,Null,Key,Default
APP_UID,varchar(32),NO,PRI,null
APP_NUMBER,int(11),NO,,null
APP_STATUS,varchar(10),NO,,null
PROJECT_TITLE,varchar(255),YES,,null
PROJECT_CHARTER_NUMBER,varchar(32),YES,,null
COMPANY_CITY,varchar(32),YES,,null
COMPANY_COUNTRY,varchar(32),YES,,null
COMPANY_NAME,varchar(255),YES,,null
PROJECT_CHARTER_CLIENT_TYPE_LABEL,varchar(32),YES,,null
PROJECT_CHARTER_CLIENT_SECTOR_OTHER,varchar(255),YES,,null
PROJECT_CHARTER_CLIENT_SECTOR_LABEL,varchar(32),YES,,null
PROJECT_CHARTER_START_DATE,varchar(10),YES,,null
REQUEST_DATE,varchar(10),YES,,null
INQUIRY_DIV,varchar(32),YES,,null
CLIENT_CALLBACK_EXPLAIN,varchar(255),YES,,null
CLIENT_CALLBACK_LABEL,varchar(32),YES,,null
CLIENT_SURVEY_LABEL,varchar(32),YES,,null
PROJECT_CLOSEOUT_FEEDBACK_LABEL,varchar(32),YES,,null
PROJECT_CLOSEOUT_SURVEY_COMMENTS,varchar(255),YES,,null
PROJECT_COMPLETION_SIGNOFF_COMMENTS_PM,varchar(255),YES,,null
PROJECT_LOST_SIGNOFF_COMMENTS_PM,varchar(255),YES,,null

I would like to include fields from the report table in the equipment list where the APP_UID is identical.

Here's the challenge: the data is constantly being updated and I am passing the query to a reporting tool, so I'm limited to a single statement.

Is there a way to combine the two sets of tables and join them using one statement? If not, is there an alternative approach that I should consider?

Any hints would be appreciated!

Upvotes: 0

Views: 1447

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Note that your second query is functionally identical to the following:

SELECT * FROM PMT_RPT_OLD
UNION
SELECT * FROM PMT_RPT_X
ORDER BY APP_NUMBER

Upvotes: 1

Beginner
Beginner

Reputation: 4153

Yes there's a way to join 2 union select statements using mysql

Just like a normal join

Example :

Select * from tbl1 join tbl2 on tbl1.linkcolumn = tbl2.linkcolumn

Just simply replace tbl1/tbl2 to your Select union statement and add an alias to it.

SELECT * 
FROM   (SELECT * 
        FROM   (SELECT * 
                FROM   pmt_equipment_used_old 
                UNION 
                SELECT * 
                FROM   pmt_equipment_used_x) AS U 
        WHERE  U.grid_equipment_id IS NOT NULL 
               AND U.app_status <> 'CANCELLED' 
        ORDER  BY U.app_number, 
                  U.row) AS TBL1 
       JOIN (SELECT * 
             FROM   (SELECT * 
                     FROM   pmt_rpt_old 
                     UNION 
                     SELECT * 
                     FROM   pmt_rpt_x) AS A 
             ORDER  BY A.app_number) AS TBL2 
         ON TBL1.app_uid = TBL2.app_uid

Also there's no need to wrap your union into a subquery since this 2 select statement which is union have same table columns.

Put your ORDER BY after the JOIN statement

SELECT * 
FROM   (SELECT * 
                FROM   pmt_equipment_used_old 
                UNION 
        SELECT * 
                FROM   pmt_equipment_used_x
        WHERE  grid_equipment_id IS NOT NULL 
               AND app_status <> 'CANCELLED') 
               AS TBL1 
       JOIN (SELECT * 
                     FROM   pmt_rpt_old 
                     UNION 
             SELECT * 
                     FROM   pmt_rpt_x
             ORDER  BY app_number) AS TBL2 
         ON TBL1.app_uid = TBL2.app_uid 
         ORDER BY TBL1.app_number, TBL1.row

Upvotes: 1

Related Questions