php - Need to find product details with minimum price -

 tbl_product:                              +------+------+------------+-------+      | id   | code | name  |brand|origin| +------+------+------------+-------+ |    1 | 1001 | apple | x   |    | |    2 | 1002 | mango | v   | b    | |    3 | 1003 | banana| z   |    | +------+------+------------+-------+ tbl_product_price: +------+------+------+ | id   | code | price| +------+------+------+ |    1 | 1001 |  250 | |    2 | 1001 |  220 |  |    3 | 1002 |  175 | |    4 | 1002 |  180 | |    5 | 1003 |  170 | |    6 | 1003 |  190 |   +------+------+------+ 

i have search box , if select pro

select a.id, a.pro_code, a.pro_unit, min(b.pro_price) tab_product inner join tab_product_price b pro_code like('" .$search . "%') order pro_code limit 5" no pro_code display in search box.

please code ok!!

first thing join 2 tables since price of product belong tbl_product_price. after joining, need use aggregate function min() , group by lowest price in every group.

select  a.id, a.code, a.name, min(b.price) minimumprice    tbl_product         inner join tbl_product_price b             on a.code = b.code group   a.id, a.code, a.name 

• sqlfiddle demo

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins

output

╔════╦══════╦════════╦══════════════╗ ║ id ║ code ║  name  ║ minimumprice ║ ╠════╬══════╬════════╬══════════════╣ ║  1 ║ 1001 ║ apple  ║          220 ║ ║  2 ║ 1002 ║ mango  ║          175 ║ ║  3 ║ 1003 ║ banana ║          170 ║ ╚════╩══════╩════════╩══════════════╝