sql - 用条件连接两个表！ 笨

Question

I really don't know how to explain my problem, so i'm gonna provide an example:

i have two tables:

Packages:

  pack_id , pack_name
    100   , Kish Island Tour
    101   , Ancient Persia Tour
    102   , Persian Gulf Tour

Package_plan:

  plan_id , pack_id , double_bed_price
    1     ,   100   ,  100$
    2     ,   100   ,   90$
    3     ,   100   ,  100$
    4     ,   101   ,  150$
    5     ,   101   ,  180$
    6     ,   102   ,   80$
    7     ,   102   ,  110$

I want to join these two tables On pack_id , and retrieve least value of double_bed_price for each packages !

more illustration , the required answer is:

  package_id , plan_id , double_bed_price
      100    ,    2    ,    90$
      101    ,    4    ,    150$
      102    ,    6    ,    80$

I'm using PHP Codeigniter , and its preferred to code in active record, but there is no problem with simple SQL query.

so many thanks to all

Answer 1

try this:

select pp.pack_id , pp.plan_id , min(double_bed_price)
from Packages p join Package_plan pp on p.pack_id = pp.pack_id
group by p.pack_id

Assuming that the double_bed_price datatype is Numeric Data Type.

If String Type then remove $ character at the end and convert to numeric data type to get correct min value.

CONVERT(REPLACE(double_bed_price, '$', ''), UNSIGNED INTEGER)