How do i select all in TABLE1 and join with matching MAX(id) from TABLE2?

I want to join TABLE1 with TABLE2, but only getting the highest id matching in TABLE2. TABLE1 will always only have 1 occurrence, while TABLE2 would have multiple occurrences, and i only want the row with the highest id.

Example:

TABLE1:

+----+------+
| id | name |
+----+------+
|  1 |   a  |
+----+------+

TABLE2

    +----+-----------+------+-------+-------+
    | id | table1_id | text | user1 | user2 |
    +----+-----------+------+-------+-------+
    |  1 |     1     |  aaa |   1   |   2   |
    +----+-----------+------+-------+-------+
    |  2 |     1     |  bbb |   2   |   1   |
    +----+-----------+------+-------+-------+

And this is what i want to get out:

+-----------+-----------+-----------+------+------+
| table1.id | table2.id | table1_id | text | name |
+-----------+-----------+-----------+------+------+
|     1     |     2     |     1     |  bbb |   a  |
+-----------+-----------+-----------+------+------+

I have tried with this:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id WHERE user1 = '1' OR user2 = '1'

And the output is:

+-----------+-----------+-----------+------+------+
| table1.id | table2.id | table1_id | text | name |
+-----------+-----------+-----------+------+------+
|     1     |     1     |     1     |  aaa |   a  |
+-----------+-----------+-----------+------+------+

But his gives me the row with the lowest id in TABLE2, and i want the row with the highest id. How can i do that?

Answer

One method puts the “maximum” condition in the ON clause:

SELECT t1.*, t2.*
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.id = t1.table1_id AND
        t2.id = (SELECT MAX(tt2.id) FROM table2 tt2 WHERE tt2.table1_id = t2.table1_id)
WHERE 1 IN (t1.user1, t1.user2);

Advertisement

Answer