Why Is This Wordpress Query Returning 18 Results Instead of Three-CodePudding

I want to find users whose usernames start with "developer"; however, when I used the query below, I got 18 results when only two should return, based on the number of users in the database. Is there something wrong with the query that is causing this?

SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login
FROM wp_users
INNER JOIN wp_usermeta
    ON ( wp_users.ID = wp_usermeta.user_id )
WHERE wp_users.ID !='1' AND wp_users.user_login LIKE 'developer%' OR (wp_usermeta.meta_key = 'nickname' AND wp_usermeta.meta_value LIKE 'developer%') OR (wp_usermeta.meta_key = 'first_name' AND wp_usermeta.meta_value LIKE 'developer%')
ORDER BY user_login ASC

The actual results:

Array (
    [0] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [1] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [2] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [3] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [4] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [5] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [6] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [7] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [8] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [9] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 ) 
    [10] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [11] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [12] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [13] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [14] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [15] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [16] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [17] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
    [18] => stdClass Object ( [ID] => 6 [user_login] => developer [id] => 6 )
)

CodePudding user response：

Do all users have wp_usermeta entries for nickname and first_name? If not, you may need to change the join to a LEFT JOIN.

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_users.ID, wp_users.user_login
FROM wp_users
INNER JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id
    AND wp_usermeta.meta_key IN ('nickname', 'first_name')
WHERE wp_users.ID != 1
AND ( wp_users.user_login LIKE 'developer%' OR wp_usermeta.meta_value LIKE 'developer%')
ORDER BY user_login ASC