CODEWITHSUNDEEP
mysqlunion
Jjreina
Jjreina

Reputation: 2713

query in order to union 2 tables on mysql

I´m working with mysql 5.5 and I have 2 tables, below 

+--------+---------------+---------------+
| CALLID | DL_THROUGHPUT | UL_THROUGHPUT |
+--------+---------------+---------------+
|    115 |       33.3333 |       43.3333 |
|    116 |            70 |            80 |
+--------+---------------+---------------+

+--------+----------------+----------------+
| CALLID | DL_DATA_VOLUME | UL_DATA_VOLUME |
+--------+----------------+----------------+
|    117 |             45 |             35 |
+--------+----------------+----------------+

with full join I get this:

+--------+---------------+---------------+--------+----------------+----------------+
| CALLID | DL_THROUGHPUT | UL_THROUGHPUT | CALLID | DL_DATA_VOLUME | UL_DATA_VOLUME |
+--------+---------------+---------------+--------+----------------+----------------+
|    115 |       33.3333 |       43.3333 |   NULL |           NULL |           NULL |
|    116 |            70 |            80 |   NULL |           NULL |           NULL |
|   NULL |          NULL |          NULL |    117 |             45 |             35 |
+--------+---------------+---------------+--------+----------------+---------------+

and I need this:

 +--------+---------------+--------------+-----------------+----------------+
| CALLID | DL_THROUGHPUT | UL_THROUGHPUT |  DL_DATA_VOLUME | UL_DATA_VOLUME |
+--------+---------------+---------------+-----------------+----------------+
|    115 |       33.3333 |       43.3333 |            NULL |           NULL |
|    116 |            70 |            80 |            NULL |           NULL |
|    117 |          NULL |          NULL |              45 |             35 |
+--------+---------------+---------------+-----------------+---------------+

Any suggestions?

Thanks you very much Thanks you Zane, sorry but before I don´t explain well also I can to have case as:

+--------+---------------+---------------+
| CALLID | DL_THROUGHPUT | UL_THROUGHPUT |
+--------+---------------+---------------+
|    115 |       33.3333 |       43.3333 |
|    116 |            70 |            80 |
|    117 |            48 |            51 |
+--------+---------------+---------------+

+--------+----------------+----------------+
| CALLID | DL_DATA_VOLUME | UL_DATA_VOLUME |
+--------+----------------+----------------+
|    117 |             45 |             35 |
|    118 |             37 |             26 |
+--------+----------------+----------------+

and I will need: 

 +--------+---------------+--------------+-----------------+----------------+
| CALLID | DL_THROUGHPUT | UL_THROUGHPUT |  DL_DATA_VOLUME | UL_DATA_VOLUME |
+--------+---------------+---------------+-----------------+----------------+
|    115 |       33.3333 |       43.3333 |            NULL |           NULL |
|    116 |            70 |            80 |            NULL |           NULL |
|    117 |            48 |            51 |              45 |             35 |
|    118 |          NULL |          NULL |              37 |             26 |        
+--------+---------------+---------------+-----------------+---------------+

Upvotes: 0

Views: 50

Answers (3)

Zane Bien
Zane Bien

Reputation: 23135

You can manually insert NULL values where you know they would be not applicable in the final result:

SELECT CALLID, 
       DL_THROUGHPUT, 
       UL_THROUGHPUT, 
       NULL AS DL_DATA_VOLUME,
       NULL AS UL_DATA_VOLUME
FROM   tbl_1

UNION ALL

SELECT CALLID, 
       NULL, 
       NULL, 
       DL_DATA_VOLUME, 
       UL_DATA_VOLUME
FROM   tbl_2

Update:

You can use:

SELECT    a.CALLID,
          b.DL_THROUGHPUT, 
          b.UL_THROUGHPUT, 
          c.DL_DATA_VOLUME, 
          c.UL_DATA_VOLUME
FROM      (
          SELECT CALLID FROM tbl_1 UNION SELECT CALLID FROM tbl_2
          ) a
LEFT JOIN tbl_1 b ON a.CALLID = b.CALLID
LEFT JOIN tbl_2 c ON a.CALLID = c.CALLID
ORDER BY  a.CALLID

SQLFiddle Demo

Upvotes: 3

Benoit
Benoit

Reputation: 79233

If you need a join, I would go for:

SELECT callid, dl_throughput, ul_throughput, dl_data_volume, ul_data_volume
  FROM table1
  FULL JOIN table2 USING(callid)

Upvotes: 0

Devart
Devart

Reputation: 122032

Try this query -

SELECT
  t.CALLID,
  t1.DL_THROUGHPUT,
  t2.UL_THROUGHPUT,
  t1.DL_DATA_VOLUME,
  t2.UL_DATA_VOLUME
FROM (SELECT CALLID FROM table1 UNION SELECT CALLID FROM table2) t
LEFT JOIN table1 t1
  ON t1.CALLID = t.CALLID
LEFT JOIN table2 t2
  ON t2.CALLID = t.CALLID

Upvotes: 1

Related Questions