user2250569 user2250569 - 2 months ago 11
SQL Question

MySQL JOIN two tables with different number of rows by id

I have two tables.

Table 1

+----+--------+
| Id | Column |
+----+--------+
| 1 | 23 |
+----+--------+
| 2 | 34 |
+----+--------+
| 3 | 99 |
+----+--------+

Table 2

+----+--------+
| Id | Column |
+----+--------+
| 10 | 1 |
+----+--------+
| 11 | 1 |
+----+--------+
| 21 | 2 |
+----+--------+
| 33 | 3 |
+----+--------+


I want to combine these tables to get

Table 3 (Desired)

+--------+--------+
| Column | Column |
+--------+--------+
| 10 | 23 |
+--------+--------+
| 11 | 23 |
+--------+--------+
| 21 | 34 |
+--------+--------+
| 33 | 99 |
+--------+--------+


I know how to get this:

Table 3 (Actual)

+--------+--------+
| Column | Column |
+--------+--------+
| 10 | 23 |
+--------+--------+
| 11 | null |
+--------+--------+
| 21 | 34 |
+--------+--------+
| 33 | 99 |
+--------+--------+


but when the rows have duplicate Id columns, the first is the correct value and
the rest are always NULL.

The query I have to produce Table 3 is

SELECT table2.id, table1.Column
FROM table2
LEFT JOIN (SELECT .... ) as table1 ON table1.id = table2.Column


How can I join tables to get the correct version of Table 3?

Answer

This should work fine:

SELECT 
  t2.id     AS column1,
  t1.column AS column2
FROM table2 AS t2
LEFT JOIN table1 AS t1 ON t1.id = t2.column;

See it in action here:

This will give you the same result you want:

| COLUMN1 | COLUMN2 |
---------------------
|      10 |      23 |
|      11 |      23 |
|      21 |      34 |
|      33 |      99 |