run two queries one return

[eluser]tim1965[/eluser]
Hi

Ok i will start from the beginning.
I have a query that works correctly. I am trying to get a min and max back from anadditional table. So i ahve added the select_max at the front and a join (the last one for "V_charges"
The new query is

Code:

$this->db->select_max('v_charges_date_price.price_per_week_1');
$this->db->select('master_property_reference.property_id, master_property_details.provinceorcounty, master_property_details.contact_country, master_property_details.region, master_property_details.property_sleeps, master_property_details.townorcity, master_property_details.property_name, master_property_details.property_highlights, master_property_details.property_type, master_property_details.floorspace, master_property_details.no_of_bedrooms, master_property_details.no_of_bathrooms, master_pictures.thumbnail, master_charges.currency, master_contactdetails.adverttype');$this->db->from('master_property_reference');
$this->db->join('master_property_details', 'master_property_reference.property_id =master_property_details.property_id');
$this->db->join('master_pictures', 'master_property_reference.property_id = master_pictures.property_id AND master_pictures.photo_desc = "main"');
$this->db->join('master_charges', 'master_property_reference.property_id = master_charges.property_id');
$this->db->join('master_contactdetails', 'master_property_reference.property_id = master_contactdetails.property_id');
$this->db->join('v_charges_date_price', 'master_property_reference.property_id = v_charges_date_price.property_id');
$this->db->where('ad_completed',$t);
$this->db->like('master_property_details.contact_country',$country);
$this->db->like('master_property_details.region',$region);
$this->db->where('master_property_details.property_sleeps <=',$property_sleeps);
$this->db->where('master_property_details.no_of_bedrooms >=',$no_of_bedrooms);
$this->db->like('master_property_details.complex_type',$complex_type);
$this->db->like('master_property_details.broadband',$broadband);
$this->db->like('master_property_details.cable',$cable);
$this->db->like('master_property_details.outside_pool',$outside_pool);
$this->db->like('master_property_details.indoor_pool',$indoor_pool);
$this->db->like('master_property_details.shared_pool',$communal_pool);
$this->db->like('master_property_details.jacuzzi_or_hot_tub',$jacuzzi);
$this->db->like('master_property_details.cot',$cot);
$this->db->like('master_property_details.suitability_young_children',$small_children);
$this->db->like('master_property_details.suitability_wheelchair',$wheelchairs);
$this->db->like('master_property_details.suitability_pets_allowed',$pets);
$this->db->like('master_property_details.canoeing', $canoeing);
$this->db->like('master_property_details.cycling', $cycling);
$this->db->like('master_property_details.golf', $golf);
$this->db->like('master_property_details.horseriding', $horse_riding);
$this->db->like('master_property_details.Mountaineering', $Mountaineering);
$this->db->like('master_property_details.para_gliding', $paragliding);
$this->db->like('master_property_details.sailing', $sailing);
$this->db->like('master_property_details.scuba_diving', $scuba_diving);
$this->db->like('master_property_details.sea_fishing', $sea_fishing);
$this->db->like('master_property_details.snow_skiing', $skiing);
$this->db->like('master_property_details.surfing', $surfing);
$this->db->like('master_property_details.tennis', $tennis);
$this->db->like('master_property_details.walking', $walking);
$this->db->like('master_property_details.wind_surfing', $wind_surfing);
$this->db->like('master_property_details.young_children', $young_children);
$this->db->like('master_property_details.teenagers', $teenagers);
$this->db->like('master_property_details.eighteen', $eighteen);
$this->db->like('master_property_details.romantic_couples', $romantic_couple);
$this->db->like('master_property_details.lively', $lively);
$this->db->like('master_property_details.relaxing', $relaxing);
$this->db->like('master_property_details.picturesque', $picturesque);
$this->db->like('master_property_details.romantic', $romantic);
$this->db->like('master_property_details.historic', $historic);
$this->db->like('master_property_details.spa', $spa);
$this->db->like('master_property_details.sophisticated', $sophisticated);
$this->db->like('master_property_details.activity', $activity);
$this->db->like('master_property_details.inexpensive', $inexpensive);
$this->db->like('master_property_details.by_the_beach', $by_the_beach);
$this->db->like('master_property_details.near_the_coast', $near_the_coast);
$this->db->like('master_property_details.resort', $resort);
$this->db->like('master_property_details.lakeside', $lakeside);
$this->db->like('master_property_details.mountainous', $mountainous);
$this->db->like('master_property_details.rural', $rural);
$this->db->like('master_property_details.village', $village);
$this->db->like('master_property_details.citycentre', $city_centre);
$this->db->like('master_property_details.waterfront', $waterfront);
$this->db->limit($limit,$offset);
$query = $this->db->get();

if($query->num_rows() >0)
{
$row=$query->result_array();
return $row;
}    
}

If i run this the SQL bombs out because the active record select_max tries to do this to the column name (relevant part of query)

SELECT `MAX(`v_charges_date_price`.`price_per_week_1`)` AS price_per_week_1,

it should look like this (note ' around MAX and the join and column name)

SELECT MAX(v_charges_date_price.price_per_week_1) AS price_per_week_1,

This is a limitation of active record as it doesnt handle JOIN well i think.
Any help appreciated