[eluser]RobertB.[/eluser]
Hello guys,
I'm trying to find another way to execute this code to not use UNION because it can not be used with active records.
Code:
(SELECT s.state, SUM(b.is_active) AS active
FROM dr_states AS s
LEFT JOIN dr_businesses AS b ON s.id = b.state_id
LEFT JOIN dr_subcat2bus AS sb ON b.id = sb.bus_id
LEFT JOIN dr_subcats AS sc ON sb.subcat_id = sc.id
WHERE sb.subcat_id = 2)
UNION
(SELECT s.state, b.is_active
FROM dr_states AS s
LEFT JOIN dr_businesses AS b ON s.id = b.state_id
LEFT JOIN dr_subcat2bus AS sb ON b.id = sb.bus_id
LEFT JOIN dr_subcats AS sc ON sb.subcat_id = sc.id
WHERE s.country_id = 1
GROUP BY s.id)
This shows all the states with the number of active business in that subcat and also shows the empty states
I was trying to use it like this but does not work
Code:
$this->db->select('s.id, s.state, SUM(b.is_active) AS active');
$this->db->from('states AS s');
$this->db->join('businesses AS b','s.id = b.state_id', 'left');
$this->db->join('subcat2bus AS sb', 'b.id = sb.bus_id', 'left');
$this->db->join('subcats AS sc', 'sb.subcat_id = sc.id', 'left');
$this->db->where('b.subcat_id', $subcat_id);
$this->db->query('UNION');
$this->db->select('s.id, s.state, b.is_active');
$this->db->from('states AS s');
$this->db->join('businesses AS b','s.id = b.state_id', 'left');
$this->db->join('subcat2bus AS sb', 'b.id = sb.bus_id', 'left');
$this->db->join('subcats AS sc', 'sb.subcat_id = sc.id', 'left');
$this->db->where('s.country_id', 1);