asked on
I have 2 MySQL tables:
Customers (master)
+----+------------+
| ID | Name |
+----+------------+
| 1 | Customer 1 |
+----+------------+
| 2 | Customer 2 |
+----+------------+
| .. | ... |
+----+------------+
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 |
+----+------------+-------+-------------+--------+
| .. | | | | |
+----+------------+-------+-------------+--------+
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
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 |
+----+------------+----+------------+-------+-------------+--------+
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