SQL - how to remove whole row if one of the column in subquery return NULL

Question

I am stuck in 1 SQL query

SELECT u.*,

um2.meta_value as parent_user_id,
( select u.user_email FROM wp_users u WHERE u.ID = um2.meta_value ) AS parent_user_email

FROM

wp_users u 

JOIN wp_usermeta um2 ON u.ID = um2.user_id
AND um2.meta_key = 'parent_user_id'

GROUP BY
u.ID

This query return 4 row ( As shown in the screenshot )

I want a scenario like : If subquery return NULL , then the whole row will not be shown. So in this example "childthree" should not be shown , as "parent_user_email" is NULL , so the whole 3rd row need to remove

Answer 1

Use a join instead:

SELECT u.*, um2.meta_value as parent_user_id,
       u2.user_email as parent_user_email
FROM wp_users u JOIN
     wp_usermeta um2
     ON u.ID = um2.user_id AND
        um2.meta_key = 'parent_user_id' JOIN
     wp_users u2
     ON u2.ID = um2.meta_value
GROUP BY u.ID;

Note: This assumes that the email value itself is never NULL. If that is possible, add WHERE u2.user_email IS NOT NULL.

Also, your query should fail because the GROUP BY columns are inconsistent with the SELECT. However, logically it seems ok, because there is only one parent and user email per user. However, I would include those columns in the GROUP BY.