MySQL join query for 2 tables: incorrect return

Question

I tried to search posts, without any result either, maybe I didn't use the right words.

I need a solution in MySQL (5.5.62).

I have two tables

+------------+----------+---------------+
| Ln         | Ln1      | Cn            |
+------------+----------+---------------+
| 17149      | E.W.T.N. | 1400-1-385171 |
| 24210      | C.A.T.R. | 1400-1-385171 |
+------------+----------+---------------+
2 rows in set


+---------------+------------+-----------+
| Nm            | Ln         | Ln1       |
+---------------+------------+-----------+
| 1400-1-385171 | 35955      | C.E.S.N.  |
| 1400-1-385171 | 35955      | C.E.S.N.  |
+---------------+------------+-----------+
2 rows in set

I need do a select it will return

+------------+----------+---------------+
| Ln         | Ln1      | Cn            |
+------------+----------+---------------+
| 17149      | E.W.T.N. | 1400-1-385171 |
| 24210      | C.A.T.R. | 1400-1-385171 |
| 35955      | C.E.S.N. | 1400-1-385171 |
+------------+----------+---------------+

I have try this query

mysql> SELECT
    Ln,
    Ln1,
    Cn
FROM
    `tbl_1` t
JOIN `tbl_2` m ON t.Cn= m.Nm
WHERE
    Cn IN ('1400-1-385171')
GROUP BY
    Ln
ORDER BY
    Ln ASC;

But the return is

+------------+----------+---------------+
| Ln         | Ln1      | Cn            |
+------------+----------+---------------+
| 17149      | E.W.T.N. | 1400-1-385171 |
| 24210      | C.A.T.R. | 1400-1-385171 |
+------------+----------+---------------+
2 rows in set

How to do resolve this?

Answer 1

It sounds like you need to UNION the two result sets into one:

SELECT  Ln,
        Ln1,
        Cn
  FROM  `tbl_1`
UNION
SELECT  Ln,
        Ln1,
        Nm
  FROM  `tbl_2`;

UNION joins both result sets together as if they were a single table. This combines results from both tables.

Using UNION as opposed to UNION ALL will cause exact duplicates to be removed from the combined result set.

More information about how to use UNION is in the official docs here.