Join 2 tables and sum with condition in codeigniter

Question

I have 2 tables

Table1: customers:
-------------
| id | name |
-------------
| 1  | Mark |
-------------
| 2  | Tom |
-------------
| 3  | John |

Table2: sales:
-----------------------------------
|sid | customerid | price | state | 
-----------------------------------
| 10 | 1          | 12000 | 0     | 
-----------------------------------
| 11 | 2          | 13500 | 1     | 
-----------------------------------
| 12 | 2          | 23000 | 1     | 
-----------------------------------
| 13 | 3          | 26000 | 0     | 
-----------------------------------
| 14 | 1          | 66000 | 1     | 
-----------------------------------

the state column is 0=no dep  and 1=dept

I want to list the customers that have DEPT by checking them in the sales table. Now i'm looping the customers and checking them one by one. and it works! but when the number of rows in the customer table grows the page slows down. i want to make this by an SQL query. can anyone help me please ?

the result will be like this:

Mark  66000
Tom   36500

Answer 1

By the Following query, you will get the same output as you want. The joining of tables will be executed on the filtered data using where condition

$this->db->select('customers.name,sum(sales.price)')
->from('customers')
->join('sales','sales.customerid = customers.id','left')
->where('sales.state !=0')
->group_by('customers.name');
->get()->result_array();