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
Advertisement
Answer
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
.