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'