I am stucked at below situation. Need expert advice. I have 3 tables, login_details,deleted_login_details and custom_fields as below. login_details structure = deleted_login_details custom-fields have : user_id,custom_name,custom_value
I want following result. I want only those users who fulfilled all where conditions with multiple joins on custom_fields table. Example
select user_id from login_details union deleted-login_details as login_table join custom_fields as f1 on f1.user_id = login_table.user_id join custom_fields as f2 on f2.user_id = login_table.user_id where f1.custom_name = '1' and f1.custom_value in (1,2,3) and f2.custom_name = '2' and f2.custom_value in (4,5,6)
So only those records should come who have custom_name =1 and value as above and custom_name=2 and value in above. But I want this using only 1 join to custom_fields table.
So only 1 ,2 should get and no 3 as 3 dont have custom_name =1 record
Any advice? Thanks in advance.