Skip to content
Advertisement

Where condition not working on MySQL union 2 tables

Sorted!!!

I have 2 tables, One table have person in charge for subscription. Second table have product users for each subscription and their details. I union both tables in mysql and query working 100% fine but when i try to filter records using where condition it return all the records without filtering.

Below you can find my query!

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'

Can someone helps me?

Advertisement

Answer

Try to wrap it and it work

 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'
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement