Using AND and bracket grouping in SQL not working

Question

I am trying to combine two conditions in a query but it isn't working. but running the conditions independently works.

LEt me explain. When I run this query:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
ORDER BY
    user_registered DESC

I get this result

And when I run this

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE ((wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

I get this result

As you can see, the user with ID 46 is common in both query result.

I only want to show users that matches both query conditions. In this case, user ID 46 only.

When I then combined them like so, I get nothing:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
            
AND((wp_usermeta.meta_key = 'car_type'
    AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

This has been driving me nuts for hours now. Please help me figure out what is wrong.

Answer 1

You need 2 table joins since there are effectively 2 meta keys in your WHERE clause:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
        INNER JOIN wp_usermeta AS um1 ON (wp_users.ID = um1.user_id)
        INNER JOIN wp_usermeta AS um2 ON (wp_users.ID = um2.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (um1.meta_key = 'first_name'
            AND um1.meta_value LIKE '%little%')
        OR (um1.meta_key = 'last_name'
            AND um1.meta_value LIKE '%little%'))

  AND((um2.meta_key = 'car_type'
    AND um2.meta_value LIKE '%Camry%')
    OR(um2.meta_key = 'car_type'
        AND um2.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC