Selecting DISTINCT with UNION

Question

My site lets users refer other users, and in some instances gives them reward points when a referred user does certain things.

I want to show the referring user (let's call her Amy, she has the user ID of 3) who she referred and how many points she was rewarded.

This query gets the users who Amy referred, plus the points they generated for her. It's working correctly.

SELECT users.username, users.created_at, SUM(reward_total) 
AS total 
FROM referral_points 
JOIN users ON users.id = referral_points.user_id 
WHERE referral_points.referred_by = 3
GROUP BY user_id

However, there are some users who signed up that didn't do anything to generate points for Amy, but I still want to show her who they are.

This query gets ALL of the users who Amy referred, regardless of points.

SELECT users.username, users.created_at, users.updated_at 
FROM users 
WHERE users.referred_by = 3

I'm having a difficult time using a UNION to join the queries. They produce results, but the results are duplicated, as I get the users from referral_points AND the same users from users

Answer 1

You do not need a union for this, this is a case for a left join:

SELECT users.username, users.created_at, SUM(referral_points.reward_total) AS total 
FROM users 
LEFT JOIN referral_points ON users.id = referral_points.user_id 
WHERE users.referred_by = 3 
GROUP BY users.user_id

It will give you all the users that Amy referred, no matter if they have referral points (e.g. not matter if there is anything in the left table for this user), and for those who have, they get summed.

This requires users.user_id to be your primary key, otherwise users.username and users.created_at in your select are ill-defined.

This also assumes that the value for referral_points.referred_by and users.referred_by is the same (or a typo in your question and you meant users.referred_by there) - in fact you should not (also) have that value in referral_points then, as it is a duplicate and could be ambigous. But just in case it is intentionally (e.g. if it can be different than the value in users.referred_by), to get the same result as your original query you can use:

...
WHERE users.referred_by = 3 and referral_points.user_id is null
  or referral_points.referred_by = 3
GROUP BY users.user_id

It lists all users that are either referred by Amy (users.referred_by) and have no referral points referred by Amy (referral_points.referred_by), or that have referral points referred by Amy (referral_points.referred_by), no matter what the value in users.referred_by is.