Retrieve data from a complex table

Question

While searches the date range, start date (date_reg) and end date (date_reg) , the mysql result should be have each main_table rows contains latest return, received, balance of each products.

E.g.: Between 10-01-2014 and 10-05-2014, should retrieve values of each product within the date

Client Id | Return         |  Received       |  Balance
          | prod 1  prod 2 |  prod 1  prod 2 |  prod 1  prod 2
--------------------------------------------------------------
1         | 2 [3]   2  [7] |  5       5      |  8       5
2         | 1 [5]   0  [8] |  5       5      |  9       3
3         | 0 [6]   1  [10]|  5       5      |  7       6

[id], where id is the primary key of sub_table

---

I have tried mysql query

   SELECT p.product_name, ipd.id as ipd_id, i.id as i_id, ipd.*, i.*
   FROM main_table i 
   LEFT JOIN sub_table ipd ON ipd.main_table_id=i.id AND ipd.product_id IN (1,2) 
   LEFT JOIN product p ON ipd.product_id=p.id 
   WHERE ipd.date_reg IN (SELECT MAX(ipd1.date_reg) 
                       FROM sub_table ipd1 
                       WHERE ipd1.main_table_id=i.id  AND 
                       date_reg BETWEEN '10-01-2014' AND '10-05-2014')  
    ORDER BY cl.id ASC LIMIT 0, 20

it only return single product of return, received and balance of each client

Answer 1

I got the output. Thanks everyone for the helps.

I have used GROUP_CANCAT to concatenate the results into one string with comma seperated

 SELECT p.product_name, ipd.id as ipd_id, i.id as i_id, ipd.*, i.*,
 GROUP_CONCAT(product_id SEPARATOR ',') as group_product_id,
 GROUP_CONCAT(ipd.return SEPARATOR ',') as group_return,
 GROUP_CONCAT(ipd.received SEPARATOR ',') as group_received,
 GROUP_CONCAT(ipd.balance SEPARATOR ',') as group_balance  
 FROM main_table i 
 LEFT JOIN sub_table ipd ON ipd.main_table_id=i.id AND ipd.product_id IN (1,2) 
 LEFT JOIN product p ON ipd.product_id=p.id 
 WHERE ipd.date_reg IN (SELECT MAX(ipd1.date_reg) 
                   FROM sub_table ipd1 
                   WHERE ipd1.main_table_id=i.id  AND 
                   date_reg BETWEEN '10-01-2014' AND '10-05-2014'
                   GROUP BY ipd1.product_id)  
 ORDER BY cl.id ASC LIMIT 0, 20

The Result

 Client Id | group_product_id | group_return | group_received | group_balance
 --------------------------------------------------------------------------
 1         | 1, 2             | 2, 2         |  5,5           |  8,5
 2         | 1, 2             | 1, 0         |  5,5           |  9,3
 3         | 1, 2             | 0, 1         |  5,5           |  7,6

Then the strings can be exploded into an array.