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
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 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();Method 2
You can simply group by customer id in sales table. Code will be like this
return $this->db->select('MAX(customers.name) AS name, SUM(sales.price) as price')->join('sales', 'sales.customerid = customers.id')->where('sales.state', 1)->group_by('customers.id')->get('customers')->result();All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0