i'm having 2 tables: members and comments. I select all members, and then join comments. But in comments I'm selecting some SUM of points, and if user never commented, I can't get that user in listing?!
So how to select default value for SUM to be 0 if user never commented, or some other solution:
SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar,
SUM(c.vote_value) AS vote_value, SUM(c.best) AS best,
SUM(c.vote_value) + SUM(c.best)*10 AS total
FROM members m
LEFT JOIN comments c ON m.member_id = c.author_id
GROUP BY c.author_id
ORDER BY m.member_id DESC
LIMIT 0, 20
EDIT:
I will try to explain... So there are 2 tables, members and comments. I need listing of all users with ranking. Comments hold all votes and best answers.
So, I need listing of all users, and they score.
Members table:
member_id - username - avatar
Comments table
comment_id - author_id - vote_value - best (0 OR 1)
Also tried to select from COMMENTS and join MEMBERS, but same thing again :(
Here's one way: SELECT *, IFNULL( ( SELECT col1 FROM table1 WHERE col1 IN ('012311','0123631','091233','092111') ), 'some_value' ) AS my_col1 FROM table1; Not neccessarily copy+paste, you will have to adjust for your specific case.
It returns either field1 if the row exists, otherwise 0.
If no default value is declared explicitly, the default value is the null value. This usually makes sense because a null value can be considered to represent unknown data. In a table definition, default values are listed after the column data type.
If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.
I'm not sure why you are including the comment_id in your SELECT list if you just want users and their rankings. Do you want only their ranking on that particular comment? I'll give a solution for now that assumes you just want a full member list with rankings:
SELECT
M.member_id,
M.user_id,
M.avatar,
COALESCE(SUM(C.vote_value), 0) AS vote_value_sum,
COALESCE(SUM(C.best), 0) AS best_sum,
COALESCE(SUM(C.vote_value), 0) + SUM(C.best) * 10 AS total_value
FROM
Members M
LEFT OUTER JOIN Comments C ON
C.author_id = M.member_id
GROUP BY
M.member_id
ORDER BY
total_value DESC
LIMIT 0, 20
(this assumes that vote_value and best are NOT NULL columns or that MySQL will disregard those when calculating SUM values - I believe that it does, but I haven't tested that)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With