Avatar of Giorgio Todeschini
Giorgio Todeschini
Flag for Italy

asked on 

Join selecting only the the last row on the details table

I have 2 MySQL tables: 

Customers (master) 

+----+------------+
| ID | Name       |
+----+------------+
|  1 | Customer 1 |
+----+------------+
|  2 | Customer 2 |
+----+------------+
| .. | ...        |
+----+------------+

Open in new window


And Offers (details). 

+----+------------+-------+-------------+--------+
| ID | CustomerID | RevId | Date        | Amount |
+----+------------+-------+-------------+--------+
|  1 |          1 | NULL  | 2021-01-01  |   1000 |
+----+------------+-------+-------------+--------+
|  2 |          1 |     1 | 2021-02-01  |   2000 |
+----+------------+-------+-------------+--------+
|  3 |          2 | NULL  | 2021-01-10  |   1200 |
+----+------------+-------+-------------+--------+
|  4 |          2 | NULL  | 2021-02-10  |   2200 |
+----+------------+-------+-------------+--------+
|  5 |          2 |     4 | 2021-03-10  |   2400 |
+----+------------+-------+-------------+--------+
| .. |            |       |             |        |
+----+------------+-------+-------------+--------+

Open in new window


The RevId field, if not NULL,  indicate that the record of the Offers table is a revision of the offer with the ID=RevId in the same table.
For example, the second record of the table here above is a revision of the first one


Now, I would like to make a query like:


SELECT * FROM Customers LEFT JOIN Offers ON Customers.ID=Offers.CustomerID

Open in new window


but, when they are a revision, I want to select only the last record of the Offers table.

The result I would like to get is the following


+----+------------+----+------------+-------+-------------+--------+
| ID | Name       | ID | CustomerID | RevId | Date        | Amount |
+----+------------+----+------------+-------+-------------+--------+
|  1 | Customer 1 |  2 |          1 |     1 | 2021-02-01  |   2000 |
+----+------------+----+------------+-------+-------------+--------+
|  2 | Customer 2 |  3 |          2 | NULL  | 2021-01-10  |   1200 |
+----+------------+----+------------+-------+-------------+--------+
|  2 | Customer 2 |  5 |          2 |     4 | 2021-03-10  |   2400 |
+----+------------+----+------------+-------+-------------+--------+

Open in new window


I'm not new in SQL language, I tried a very lot of ways but I can't able to solve this issue

Could anyone please help me?


Thanks in advance


SQLMySQL Server

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon