Unable to get lowest value using join

February 27, 2023

I have two tables "info" and "sell_info", "info" table contains "list of record" and "sell_info" contains "product which are on sale",so i want to get "lowest" value of record where id="1",How can i do this ? Here is my table "info"

id              name
1               ABC
2               XYZ
3                   CDE
...

Here is my table "sell_info"

id              product_id              price
1               1                       5
2               1                       3
3               1                       8
4               2                       2
...

Expected result is (getting lowest price of id="1")

id              name        price 
1               ABC          3

>Solution :

You can achieve the desired result by joining the info and sell_info tables on the id and product_id columns, respectively, and then using the MIN() function to get the lowest price. Here’s an example query that should work:

SELECT info.id, info.name, MIN(sell_info.price) AS price
FROM info
JOIN sell_info ON info.id = sell_info.product_id
WHERE info.id = 1
GROUP BY info.id, info.name;