mysql join query with sum retuns null
Question by dr.linux
If makale_payments table is null the query returns null. How shoul i set the query yo get recors if doesnt exist in SUM(payment_total) like 0.00 ?
SELECT article_name,user_real_name,user_name,SUM(`article_payment`) as holding,paid
FROM makale_articles AS t1
JOIN makale_users ON (t1.article_name=makale_users.user_id)
JOIN (SELECT user_id,SUM(`payment_total`) as paid FROM makale_payments GROUP BY user_id) AS t2 ON (t1.article_name=t2.user_id)
GROUP BY t2.user_id
Answer by user582118
MySQL does not return aggregate values for rows that return nulls, even if specifically testing for ISNULL. ISNULL is only to check if a column is null, not if the relation returns null. I did however find a (albeit very obscure) way to get around this limitation. The basic premise is to union all two sql statements and use the two unioned query as a subquery to select from.
The unioned queryies would be of the form:
select column1, column2, SUM(column3) as payment from tablea, tableb where....
union all
select column1, column2, 0.00 as payment from tablea --notice no join, and a specified value and alias for aggregate column
With the above query, you will get two rows for every row that the relation is not null, and one row when it is. If you sum that rows and group by, then you should end up with the values you want. So you then create a query that uses the above as the table to select from and sums the aggregate column:
select column1, column2, SUM(payment) from
(select column1, column2, SUM(column3) as payment from tablea, tableb where....
union all
select column1, column2, 0.00 as payment from tablea) as b
group by column1, column2
I am probably off on the syntax of the query, sorry, I don’t have MySQL here to test.
Answer by Starx
