Codeigniter mysql query joining 2 values across 3 tables

Question

Although I can get the joins to work, it doesn't return the lowest deal possible. The result should be lowest handset price possible with the lowest monthly cost. Currently I can get the lowest handset price, but not with the lowest monthly cost and similarly lowest monthly cost but not with the lowest priced handset.

I have 3 tables DEALS, TARIFFS and HANDSETS. When a customer comes to a manufacturer product listing page, it loads all products related to that manufacturer (in most instances around 40 products). What I would like is it to load the cheapest deal available for each product. Tariff table has over 4000 records. Handset table has over 3000 records. Deals table has over 1 million records.

There is a representation of the the 3 tables below with relevant columns:

                  Handset Table
          ==================================
                  id    |    manufacturer_id
          -----------------------------------
                 100              1


                   Deals Table
==================================================
 tariff_id    |    handset_id    |    handset_cost
--------------------------------------------------
     15             100              44.99
     20             100             114.99


                 Tariffs Table
          ==============================
                 id    |    monthly_cost
          ------------------------------
                15           12.50
                20            7.50

This is the query

$this->db->select('h.name, t.monthly_cost, d.handset_cost');
        $this->db->from('aff_deals d');
        $this->db->join('aff_tariffs t', 't.id = d.tariff_id', 'inner');
        $this->db->join('aff_handsets h', 'h.id = d.handset_id', 'inner');

        if (isset($manuid)) {
            $this->db->where('h.manufacturer_id', $manuid);
        }

        $this->db->order_by('d.handset_cost ASC');
        $this->db->order_by('t.monthly_cost ASC');

        $this->db->group_by('h.name');

        $query = $this->db->get();
        $result = $query->result();

Unfortunately this returns handset cost of £114.99 and monthly cost of £7.50, when I need £44.99 and £12.50. The example above is a simple snap shot. I have tried MIN(d.handset_cost), sub-queries but cannot get the desired results. Somehow I need to be able to get the lowest price handsets, even if it's 0.00 (FREE), with its equivalent monthly cost. Any help would be most welcome.

Answer 1

According to your query you are misusing Mysql's GROUP BY extension without having any aggregate function this will lead your query to return indeterminate results for columns which are absent in group by taking your query as an example,columns t.monthly_cost, d.handset_cost values are indeterminate if you are specific to pick minimum row from deals table per handset then you can use below query

SELECT h.name,
t.monthly_cost,
d.handset_cost
FROM aff_deals d
INNER JOIN (SELECT handset_id,MIN(handset_cost) handset_cost 
            FROM aff_deals 
        GROUP BY handset_id) dd
      ON d.handset_id = dd.handset_id AND d.handset_cost = dd.handset_cost
INNER JOIN aff_tariffs t ON t.id = d.tariff_id
INNER JOIN aff_handsets h ON h.id = d.handset_id
WHERE h.manufacturer_id =1
ORDER BY d.handset_cost ASC,t.monthly_cost ASC

See Demo

For active record query it will difficult to replicate above (subselect) query bu you can directly run this query through $this->db->query('your query')