CODEWITHSUNDEEP
mysqlsqldatabase
John Richard Salt
John Richard Salt

Reputation: 1003

I want to show relational data, when theres data missing in a mysql query

I want to show relational data, when theres data missing in a mysql query , I have the following query

SELECT u.user_email, um1.value AS school, um.value AS postcode
FROM `wp_users` AS u, `wp_bp_xprofile_data` AS um, `wp_bp_xprofile_data` AS um1
WHERE u.ID = um.user_id
AND u.ID = um1.user_id
AND um.field_id =322
AND um1.field_id =69

This brings out an email address, the name of a school and a postcode. What I'm trying to figure out is how to get all the emails if either school or postcode are not in the database i.e. show values even if data is missing

Cheers, Rich

Upvotes: 2

Views: 89

Answers (1)

symcbean
symcbean

Reputation: 48357

SELECT u.user_email 
,       um1.value AS school
,       um.value AS postcode
FROM `wp_users` AS u
LEFT JOIN `wp_bp_xprofile_data` AS um
  ON u.ID = um.user_id
  AND um.field_id = 322
LEFT JOIN `wp_bp_xprofile_data` AS um1
  ON u.ID = um1.user_id
  AND um1.field_id = 69

Upvotes: 2

Related Questions