wp_users, wp_usermeta table join query not work for me

SELECT distinct(a.ID) FROM wp_users as a 
INNER JOIN wp_usermeta as b ON a.id = b.user_id
WHERE a.id < 1000 AND b.meta_key != 'user_name' AND (b.meta_key = 'first_name' AND b.meta_value = '') 

I want to find data where user_id(a.id) is less than 1000 and there is no user_name(wp_usermeta table) field in meta_key and first_name(wp_usermeta table) in meta_key is blank.

However, this query does not check for user_name data. What is wrong with my query?

I tried

SELECT distinct(a.ID)
FROM wp_users as a 
INNER JOIN wp_usermeta as b ON a.id = b.user_id 
INNER JOIN wp_usermeta as c on a.id = c.user_id 
WHERE a.id < 1000 AND c.meta_key != 'user_name' AND (b.meta_key = 'first_name' AND b.meta_value = '') 

not worked. and i tried LIKE statement and NOT IN () .. etc

I think I've used everything I can..

I'd appreciate your help.

CodePudding user response：

Grouping by a.ID is a good scenario over distinct. The SUM(expression) added up the expression value (either 0 false, or 1 true). By using HAVING as a post grouping criteria allows this to be your filter.

SELECT a.ID, SUM(meta_key = 'user_name') as un, SUM(meta_key = 'first_name' AND meta_value = '') as fn
FROM wp_users as a 
INNER JOIN wp_usermeta as b ON a.id = b.user_id
WHERE a.id < 1000
GROUP BY a.ID
HAVING un = 0 and fn = 1