SQL – LEFT OUTER JOIN adding returned null values

In a SQL with LEFT OUTER JOIN I had to sum some columns but some of them returned with NULL value and the result of it was a NULL.

So imagine that we want to add table1`.`columnA`to `table2`.`columnB`, we do something like this

SELECT *, (table1`.`columnA` + `table2`.`columnB`) AS total

and if some of them is NULL the total will be NULL.

What I had to do was this

SELECT *, (ifnull(`table1`.`columnA`,0 ) + ifnull(`table2`.`columnB`, 0)) AS total

And if you want to put it on codeigniter where is how I made it:

$this->db->select('*, (ifnull(`table1`.`columnA`,0 ) + ifnull(`table2`.`columnB`, 0)) AS total', FALSE);

Note the , FALSE, it has to be there.

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.
http://ellislab.com/codeigniter/user-guide/database/active_record.html

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.