0

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

enter image description here

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

enter image description here

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

1

1 Answer 1

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
1
  • Thanks Tim for your help.
    – W3Guy
    Jan 11, 2021 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.