How can I union all rows of two different tables using MySQL 8.0.17 version?

Databases
1

Hi,

These are two tables of my MySQL database 8.0.17

On these tables the corresponding column for each tables is the column tUnity

-- ----------------------------
-- Table structure for t_release
-- ----------------------------
DROP TABLE IF EXISTS `t_release`;
CREATE TABLE `t_release`  (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `tUnity` varchar(255) DEFAULT NULL,
  `tMonthYear` varchar(255) DEFAULT NULL,
  `tHHrelease` datetime(0) DEFAULT NULL,
  `tHHapproved` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_release
-- ----------------------------
INSERT INTO `t_release` VALUES (1, 'D41', '1-2021', '2021-02-26 16:22:19', NULL);


-- ----------------------------
-- Table structure for t_unities
-- ----------------------------
DROP TABLE IF EXISTS `t_unities`;
CREATE TABLE `t_unities`  (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `tUnity` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_unities
-- ----------------------------
INSERT INTO `t_unities` VALUES (1, 'D40');
INSERT INTO `t_unities` VALUES (2, 'D41');
INSERT INTO `t_unities` VALUES (3, 'D42');
INSERT INTO `t_unities` VALUES (4, 'D43');
INSERT INTO `t_unities` VALUES (5, 'D44');
INSERT INTO `t_unities` VALUES (6, 'D45');
INSERT INTO `t_unities` VALUES (7, 'D46');
INSERT INTO `t_unities` VALUES (8, 'D47');
INSERT INTO `t_unities` VALUES (9, 'D48');
INSERT INTO `t_unities` VALUES (10, 'D49');

I need union these tables for this return

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D40    |            |                     | NULL        | D40    |
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D42    |            |                     | NULL        | D42    |
| D43    |            |                     | NULL        | D43    |
| D44    |            |                     | NULL        | D43    |
| D45    |            |                     | NULL        | D45    |
| D46    |            |                     | NULL        | D46    |
| D47    |            |                     | NULL        | D47    |
| D48    |            |                     | NULL        | D48    |
| D49    |            |                     | NULL        | D49    |
+--------+------------+---------------------+-------------+--------+
10 rows in set (0.03 sec)

And I have tried this SQL query

SELECT DISTINCT
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    t.tUnity AS tUnity
FROM
    t_release t
    LEFT JOIN t_unities q ON LEFT ( t.tUnity, 2 ) = LEFT ( q.tUnity, 2 ) 
WHERE
    LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
    AND tMonthYear = '1-2021';

But the return from this SQL query is

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D40    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D42    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D43    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D44    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D45    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D46    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D47    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D48    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D49    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
+--------+------------+---------------------+-------------+--------+
10 rows in set (0.03 sec)

Help me to do it.

2 
SELECT q.tUnity AS qUnity
     , t.tMonthYear
     , t.tHHrelease
     , t.tHHapproved
     , t.tUnity
  FROM t_unities q 
LEFT OUTER
  JOIN t_release t
    ON t.tUnity = q.tUnity
3

thanks for reply.

on the table t_release I might have D21, D34, D58, etc and tMonthYear 1-2021, 2-2021, etc…

I have problem

SELECT
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    t.tUnity AS tUnity 
FROM
    t_unities q
    LEFT OUTER JOIN t_release t ON t.tUnity=q.tUnity
WHERE
    LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
    AND tMonthYear = '1-2021';

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
+--------+------------+---------------------+-------------+--------+
1 row in set (0.03 sec)
4

replace this –

FROM
    t_unities q
    LEFT OUTER JOIN t_release t ON t.tUnity=q.tUnity
WHERE
    LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
    AND tMonthYear = '1-2021';

with this –

  FROM t_unities q
LEFT OUTER 
  JOIN t_release t 
    ON t.tUnity = q.tUnity
   AND t.tUnity LIKE 'd4%'
 WHERE t.tMonthYear = '1-2021'

please tell my why you want to chop this query into pieces like your other ones

1 Like
5

Thanks a lot!

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.