条件在MySQL Union 2表上不起作用

debugcn 发表于 Dev

玛莉丝·帕慕迪莎·费尔南多

排序！！！

我有2张桌子，一张桌子有负责预订的人。第二个表具有每个订阅的产品用户及其详细信息。我在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'

有人可以帮我吗？

尝试将其包装起来就可以了

 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'

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。