Devs Devs - 3 years ago 141
MySQL Question

Mysql: Retrieve data from a complex table

enter image description here

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download