MySql Join table that doesn't have any matches shows null values

开发者_运维问答

Hi I'm trying to join a table like so:

  SELECT
    u.mobile_number,
    u.dob,
    u.gender,
    u.country,
    SUM(b.reward_points) AS points
  FROM users u
  JOIN bookings b ON u.user_id=b.user_id
  WHERE u.user_id=20
  AND b.attended=1
  AND b.review_completed=1
  AND b.date > "2010-03-12"
  AND b.reward_used=0

however because the bookings table has no records for user_id 20, all the columns returned have NULL value. I want to get the values for the user back and points returned NULL (when no corresponding rows are found in the bookings table). How can I make this work? What am I doing wrong?

---

You need to move all conditions on table b from the WHERE clause to the ON clause:

  SELECT
    u.mobile_number,
    u.dob,
    u.gender,
    u.country,
    SUM(b.reward_points) AS points
  FROM users u
  LEFT JOIN bookings b 
    ON u.user_id=b.user_id
    AND b.attended=1
    AND b.review_completed=1
    AND b.date > "2010-03-12"
    AND b.reward_used=0
  WHERE u.user_id=20

Explanation: The WHERE clause filters the result, no matter what type of JOIN you use - records with no match in table b can't satisfy any condition on this table so they are not returned by the query. The ON clause defines how to attach records from table b to records from table u (so only records with b.attended=1 and so on are joined).

---

Try using an INNER JOIN, instead of a JOIN, to only join if there are rows in the booking table that answer the condition u.user_id=b.user_id.

---

You can add the clause:

group by u.mobile_number, u.dob, u.gender, u.country

at the end of your original queryand also use the INNER JOIN instead of JOIN. Hope this helps.