Using AND and bracket grouping in SQL not working

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.

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating