Josef Josef - 6 months ago 9
SQL Question

Join two tables with highest ID in second table

I have two tables. First contains products, and second one contains product prices.


**Table1**
productName |
--------------
a |
b |
c |


**Table2**
productName | Price | ID |
-----------------------------
a | 3 | 1 |
b | 4 | 2 |
a | 1 | 3 |
b | 2 | 4 |
c | 1 | 5 |


I need to get products with last price. Product with last price have in second table have the highest ID.

The output should be like:


**Output**
productName | Price |
----------------------
a | 3 |
b | 2 |
c | 1 |


So far I can get only for one product but how to get list for all products

SELECT table1.productname, table2.price
FROM table1
LEFT JOIN table2 ON table1.productname = table2.productname
WHERE table1.productname = 'a' AND table1.ID = (SELECT id FROM table2 WHERE productname = 'b' ORDER BY id DESC LIMIT 1)

Answer

Try this;)

SELECT table1.productname, table2.price
FROM table1
LEFT JOIN table2 ON table1.productname = table2.productname
WHERE (table2.productname, table2.ID) in (select productName, max(id) from table2 group by productName)