CODEWITHSUNDEEP
mysqlleft-join
Thomas
Thomas

Reputation: 161

MySQL LEFT JOIN not returning NULL for missing rows

I'm probably messing it up. But I have a WordPress system where I am trying to build an export of specific user data in MySQL. I am expecting metadata to return NULL if the row doesn't exist, but instead it seems like it's acting like a limit. If any of the metadata doesn't exist the entire user is omitted. 

SELECT
  users.ID,
  users.user_email,
  _first.meta_value as firstName,
  _second.meta_value as lastName,
  users.display_name,
  _gender.meta_value as gender,
  _age.meta_value as age,
  _nationality.meta_value as nationality

FROM kp_users

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id
LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id
LEFT JOIN usermeta as _gender ON kp_users.id = _gender.user_id
LEFT JOIN usermeta as _age ON kp_users.id = _age.user_id
LEFT JOIN usermeta as _nationality ON kp_users.id = _nationality.user_id

WHERE
  _first.meta_key = 'first_name' AND
  _second.meta_key = 'last_name' AND
  _gender.meta_key = '_user_demographics_gender' AND
  _age.meta_key = '_user_demographics_age' AND
  _nationality.meta_key = '_user_demographics_nationality'

So, for metadata rows where, say, the _user_demographics_age row doesn't exist the entire user is omitted from the final set of results, and I cannot for the life of me figure out why.

Upvotes: 2

Views: 1510

Answers (3)

Robertas Murza
Robertas Murza

Reputation: 46

Move all your filters from where to left join:

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id and _first.meta_key = 'first_name' LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id and _second.meta_key = 'last_name' -- etc.

MySQL optimizer changes left join to join, if left-joined table has conditions in where part of query (that's an approximation, there are additional rules).

Upvotes: 0

Monofuse
Monofuse

Reputation: 827

Move your left join tables "where" conditions to the relative "on" conditions. At the moment your getting your null fields, but then your where condition is filtering them out because _first.meta_key is null and this doesn't equal 'firsst_name,

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

You need to put your others conditions in ON clause instead of Where Clause like below - 

SELECT
  users.ID,
  users.user_email,
  _first.meta_value as firstName,
  _second.meta_value as lastName,
  users.display_name,
  _gender.meta_value as gender,
  _age.meta_value as age,
  _nationality.meta_value as nationality

FROM kp_users

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id and _first.meta_key = 'first_name'
LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id and _second.meta_key = 'last_name'
LEFT JOIN usermeta as _gender ON kp_users.id = _gender.user_id and _gender.meta_key = '_user_demographics_gender'
LEFT JOIN usermeta as _age ON kp_users.id = _age.user_id and _age.meta_key = '_user_demographics_age'
LEFT JOIN usermeta as _nationality ON kp_users.id = _nationality.user_id and _nationality.meta_key = '_user_demographics_nationality'

Upvotes: 3

Related Questions