column must appear in the GROUP BY clause or be used in an aggregate function in inner join and outer join tables

0
votes

Im getting an error: column "u.username" must appear in the GROUP BY clause or be used in an aggregate function.

Im trying to inner join table A and table B and outer join table C ((A Inner joion B) outer Join C)

Im running the code down below. 

SELECT u.username, u.name, m.date, m.messages, u.profile_image, m.message_id,
COUNT(case current_vote when -1 then 1 else null end) AS post_down_vote,
COUNT(case current_vote when 1 then 1 else null end) AS post_up_vote
FROM Users u 
    INNER JOIN messages m ON u.user_id=m.sender_id  
    FULL OUTER JOIN message_vote v ON m.message_id = v.message_id
    WHERE receiver_id = 18;

Any help would be appreciated.

Thanks!

1
sqlpsql

1 Answers

2
votes

First, you don't need a full join. Second, you just need to include all the unaggregated columns:

SELECT u.username, u.name, m.date, m.messages, u.profile_image, m.message_id,
       SUM(case current_vote when -1 then 1 end) AS post_down_vote,
       SUM(case current_vote when 1 then 1 end) AS post_up_vote
FROM Users u INNER JOIN
     messages m
     ON u.user_id = m.sender_id LEFT JOIN
     message_vote v
     ON m.message_id = v.message_id
WHERE receiver_id = 18
GROUP BY u.username, u.name, m.date, m.messages, u.profile_image, m.message_id;