AGENT DB TABLE
agent_id agent_name company_name
-------- ---------- -----------
1 AAA XXX
2 BBB YYY
3 CCC ZZZ
4 DDD XYZ
DRIVER DB TABLE
agent_id driver_id driver_name last_viewed
-------- ---------- ----------- -----------
2 1 EEE 1
2 2 FFF 0
2 3 GGG 0
1 4 HHH 0
3 5 III 1
3 6 JJJ 1
I WANT THE OUTPUT LIKE THIS
Agent Details Driver details
1, AAA, 1 Drivers (0 active | 1 idle)
Company name
2, BBB, 3 Drivers (1 active | 2 idle)
Company name
3, CCC, 2 Drivers (2 active | 0 idle)
Company name
I have tried this below query
$sql="SELECT a.*,d.*, COUNT(d.driver_id) AS drivers_count FROM ta_agent a JOIN ta_drivers d USING(agent_id) GROUP BY a.agent_id";
I want to show active and idle status of the driver based on last_viewed
column. For example agent_id 2 have three drivers (1,2,3) and those 3 drivers have 1,0,0 in their last_viewed column. So, i want to show the output like this 1 active and 2 idle...