MySQL多表条件查询异常：country_of_origin为Any Country时无数据

Hey there! Let's break down why your query isn't returning records when country_of_origin is set to 'Any Country', and fix it up.

The Root Issue #

Looking at your WHERE clause, the condition handling country origin is:

IF(`tbui`.`country_of_origin` != 'Any Country',`tbui`.`country_of_origin` = `tbpa`.`country_of_origin`,true)

This logic checks if the filtered user's country is NOT 'Any Country'—only then does it require a match with the preference table. If the filtered user's country IS 'Any Country', it passes the condition.

But based on your problem description, it sounds like your intended logic is likely the opposite: you want to show all users if the current user's preference (tbpa.country_of_origin) is 'Any Country', otherwise only show users with a matching country. That's the typical behavior for preference-based filters!

The Fix #

Replace that IF condition with a straightforward OR clause that aligns with your actual requirement:

`tbpa`.`country_of_origin` = 'Any Country' OR `tbui`.`country_of_origin` = `tbpa`.`country_of_origin`

This does exactly what you need:

• If the current user's preference allows any country, all matching users (based on other criteria) are included.
• If the preference specifies a specific country, only users with that country are included.

Updated Full Query #

Here's your complete query with the corrected condition:

SELECT 
    `tbui`.`user_id`,
    `tbui`.`first_name`,
    `tbui`.`last_name`,
    `tbui`.`education_level`,
    `tbui`.`sex`,
    `tbui`.`country_of_origin`,
    `tbui`.`city`,
    `tbui`.`state`,
    `tbui`.`country`,
    `tbui`.`occupation`,
    `tbui`.`about`,
    TIMESTAMPDIFF(YEAR,`tbui`.`age`,CURDATE()) as age 
FROM `tb_preference_dropdown` as `tbpda` 
LEFT JOIN `tb_user_answers` as `tbua` ON `tbpda`.`question_id` = `tbua`.`question_id` 
LEFT JOIN `tb_preference_questions` as `tbpa` ON `tbpda`.`user_id` = `tbpa`.`user_id` 
LEFT JOIN `tb_user_info` as `tbui` ON `tbua`.`user_id` = `tbui`.`user_id` 
WHERE 
    `tbpda`.`user_id` = $uid 
    AND TIMESTAMPDIFF(YEAR,`tbui`.`age`,CURDATE()) >=`tbpa`.`min_age_required` 
    AND TIMESTAMPDIFF(YEAR,`tbui`.`age`,CURDATE()) <= `tbpa`.`max_age_required` 
    AND (`tbpa`.`country_of_origin` = 'Any Country' OR `tbui`.`country_of_origin` = `tbpa`.`country_of_origin`)
    AND `tbua`.`user_id` != $uid 
    AND `tbui`.`user_id` NOT IN ($matches) 
    AND `tbui`.`user_id` NOT IN ($block_users) 
    AND `tbui`.`user_id` NOT IN ($reported_user) 
    AND `tbui`.`sex` != '$gender' 
    AND IF($count > 0,`tbui`.`country` IN ($result),true) 
GROUP BY `tbui`.`user_id`

Why This Works #

• The OR clause is more readable than the IF statement, making it easier to debug and maintain.
• It directly implements your core requirement: match countries unless the preference allows any country.

If your original intention was actually to include users who have 'Any Country' set in their own profile (not the preference), then the issue might be related to NULL values in tbpa.country_of_origin. In that case, you could adjust the condition to handle NULLs with COALESCE:

`tbui`.`country_of_origin` = 'Any Country' OR `tbui`.`country_of_origin` = COALESCE(`tbpa`.`country_of_origin`, `tbui`.`country_of_origin`)

But based on your problem statement, the first fix is the most likely solution.

内容的提问来源于stack exchange，提问作者Pandat Arun