1

I need some help fixing my query to return data when there are no matching rows between tables. I understand the concept of outer joins to accomplish this but I am having difficulty getting it to return the result set I want. Here is my SQL statement.

SELECT   wp_posts.*, mmud.membership_level_id
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON wp_posts.ID = pm1.post_id
JOIN wp_postmeta AS pm2 ON wp_posts.ID = pm2.post_id
JOIN wp_postmeta AS pm3 ON wp_posts.ID = pm3.post_id
LEFT JOIN mm_user_data as mmud on mmud.wp_user_id = pm1.meta_value 
WHERE 1=1  
AND wp_posts.post_type IN ('buyer_profile', 'seller_profile') AND ((wp_posts.post_status = 'publish'))
AND pm1.meta_key = 'profile_user'
AND pm2.meta_key = 'is_profile_completed'
AND pm2.meta_value = '1'
AND pm3.meta_key = 'profile_status'
  ORDER BY FIELD(mmud.membership_level_id, '2', '5', '4', '6') DESC, 
FIELD(pm3.meta_value, 'Inactive', 'Active') DESC,
wp_posts.post_date DESC

This is the result set I am trying to achieve and the source tables. Basically my problem is that some users do not have a row in the post_meta table matching 'meta_key = profile_user' and no row in the table mm_user_data matching this missing row in post_meta table.

Result Set
-------------------------------------------------------                                                                                                                                                                                                                                                    
| ID  | post_title          | membership_level_id   |                                                                                                                                                                                                                                                     
-------------------------------------------------------                                                                                                                                                                                                                                                   
|   1 | My Title            | 4                     |                                                                                                                                                                                                                                                     
|   2 | My Other Title      | NULL                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
-------------------------------------------------------

Source Tables

wp_posts
-------------------------------                                                                                                                                                                                                                                                 
| ID  | post_title          |                                                                                                                                                                                                                                                    
-------------------------------                                                                                                                                                                                                                                                   
|   1 | My Title            |                                                                                                                                                                                                                                                     
|   2 | My Other Title      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------------------------

wp_postmeta
------------------------------------------------------------------                                                                                                                                                                                                                     
| post_id   | meta_key              |   meta_value  |  meta_id  |                                                                                                                                                                                                                                             
------------------------------------------------------------------                                                                                                                                                                                                           
|   1       | profile_user          |   1234        |   3536    |                                                                                                                                                                                                                          
|   1       | is_profile_completed  |   1           |   2838    |
|   1       |   profile_status      |   Active      |   1334    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
|   2       | is_profile_completed  |   1           |   2342    |
|   2       |   profile_status      |   Active      |   1236    |   
------------------------------------------------------------------

mm_user_data
------------------------------------------                                                                                                                                                                                                                               
| wp_user_id    | membership_level_id   |                                                                                                                                                                                                                                                    
------------------------------------------                                                                                                                                                                                                                                            
|   1234        |           4           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------

Any help would be greatly appreciated!

1

Your condition in the WHERE clause pm1.meta_key = 'profile_user' basically filter out all the rows which column meta_key is NULL. To resolve, put the condition in the ON clause.

SELECT   wp_posts.*, , mmud.membership_level_id
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON wp_posts.ID = pm1.post_id AND pm1.meta_key = 'profile_user'
JOIN wp_postmeta AS pm2 ON wp_posts.ID = pm2.post_id
JOIN wp_postmeta AS pm3 ON wp_posts.ID = pm3.post_id
LEFT JOIN mm_user_data as mmud on mmud.wp_user_id = pm1.meta_value 
WHERE 1=1  
    AND wp_posts.post_type IN ('buyer_profile', 'seller_profile') AND ((wp_posts.post_status = 'publish'))
    AND pm2.meta_key = 'is_profile_completed'
    AND pm2.meta_value = '1'
    AND pm3.meta_key = 'profile_status'
ORDER BY FIELD(mmud.membership_level_id, '2', '5', '4', '6') DESC, 
    FIELD(pm3.meta_value, 'Inactive', 'Active') DESC,
    wp_posts.post_date DESC
|improve this answer|||||
  • Thanks for the help and for the explanation! Makes perfect sense now that you've pointed it out :) – Andrew Schultz Feb 13 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.