Problem adding column values in MySQL SELECT query / sorting by added column values
Submitted by Local Tree Child on Tue, 11/06/2007 - 09:47.
I wanted to sort an sql query according to added column values. Like this
<?php
$query = "SELECT * FROM myTable1 AS my1 ".
"LEFT JOIN myTable2 AS my2 ON my2.id = my1.id ".
"LEFT JOIN myTable3 AS my3 ON my3.id = my1.id ".
"ORDER BY (my2.price + my3.price)";
?>The problem was that the order was not coming out right. It turned out that as the query made left joins some rows had null values where prices where not set. Thus when adding values null was returned. This was solved by replacing the null values with 0 with the COALESCE function. The COALESCE returns the first value in the list that is not null, and null if no none null values were found, for example
SELECT COALESCE(NULL,1)
returns 1. The query thus became
<?php
$query = "SELECT * FROM myTable1 AS my1 ".
"LEFT JOIN myTable2 AS my2 ON my2.id = my1.id ".
"LEFT JOIN myTable3 AS my3 ON my3.id = my1.id ".
"ORDER BY (COALESCE(my2.price,0) + COALESCE(my3.price,0))";
?>and voila the values were added correctly and the ordering functioned correctly.