MySQL 5.7 – OUTER JOIN to Include Rows with No Matches

MySQLmysql-5.7

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!

Best Answer

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

SUBSET QUERY #1

SELECT
    merchant.locationName,
    wp_postmeta_location.meta_value,
    wp_postmeta_location.post_id,
    merchant.brandIdentifier,
    wp_postmeta_brand.meta_value
FROM
    (
        SELECT `brandIdentifier`,`locationIdentifier`,`locationName`
        FROM merchant WHERE postalCode='90210'
        GROUP BY `locationName`,`locationIdentifier`
    ) merchant
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'locationIdentifier'
    ) wp_postmeta_location
    ON merchant.locationIdentifier = wp_postmeta_location.meta_value
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'brandIdentifier'
    ) wp_postmeta_brand
    ON
    (merchant.brandIdentifier = wp_postmeta_brand.meta_value AND
    wp_postmeta_location.post_id = wp_postmeta_brand.post_id)
;

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Mysql – How to best combine 3 queries into 1

SUBSET QUERY #1

Related Question