mysql - Where 条件不适用于 MySQL union 2 表

Question

已排序!!!

我有2张 table ，一张 table 有负责人订阅。第二张表有每个订阅的产品用户及其详细信息。我在 mysql 中联合两个表并查询 100% 正常工作但是当我尝试使用 where 条件过滤记录时它返回所有记录而不过滤。

您可以在下面找到我的查询!

SELECT subscription_products.subscription_id, users.id, users.full_name,
users.company, users.job, users.birthday, users.gender,
users.nric, users.passport_number, users.phone_country_code,
users.phone_number, users.handphone_country_code, users.handphone_number,  
users.email, users.nationality, wallets.current_amount,
users.created_at, users.updated_at

FROM subscription_product_users
LEFT JOIN subscription_products
ON subscription_product_users.subscription_product_id = subscription_products.id
LEFT JOIN users
ON subscription_product_users.user_id = users.id
LEFT JOIN wallets
ON users.id = wallets.user_id

UNION

SELECT person_in_charge.subscription_id, person_in_charge.user_id,
users.full_name,
users.company, users.job, users.birthday, users.gender,
users.nric, users.passport_number, users.phone_country_code,
users.phone_number, users.handphone_country_code,
users.handphone_number, users.email, users.nationality, wallets.current_amount,
users.created_at, users.updated_at 

FROM person_in_charge
LEFT JOIN users
ON person_in_charge.user_id = users.id
LEFT JOIN wallets
ON person_in_charge.user_id = wallets.user_id

where subscription_id = '1378'

有人可以帮助我吗？

Answer 1

尝试包装它，它起作用了

 SELECT * FROM 

    (SELECT subscription_products.subscription_id, users.id, users.full_name,
    users.company, users.job, users.birthday, users.gender,
    users.nric, users.passport_number, users.phone_country_code,
    users.phone_number, users.handphone_country_code, users.handphone_number,  
    users.email, users.nationality, wallets.current_amount,
    users.created_at, users.updated_at

    FROM subscription_product_users
    LEFT JOIN subscription_products
    ON subscription_product_users.subscription_product_id = subscription_products.id
    LEFT JOIN users
    ON subscription_product_users.user_id = users.id
    LEFT JOIN wallets
    ON users.id = wallets.user_id

    UNION

    SELECT person_in_charge.subscription_id, person_in_charge.user_id,
    users.full_name,
    users.company, users.job, users.birthday, users.gender,
    users.nric, users.passport_number, users.phone_country_code,
    users.phone_number, users.handphone_country_code,
    users.handphone_number, users.email, users.nationality, wallets.current_amount,
    users.created_at, users.updated_at 

    FROM person_in_charge
    LEFT JOIN users
    ON person_in_charge.user_id = users.id
    LEFT JOIN wallets
    ON person_in_charge.user_id = wallets.user_id) 
as tempTable

where subscription_id = '1378'