Remove multiple joins to same table with where 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.

Like as

So only 1 ,2 should get and no 3 as 3 dont have custom_name =1 record



Any advice?
Thanks in advance.
Append to your query GROUP BY user_id. This has the effect that multiple user_ids become one row entry.

Additionally append HAVING COUNT(user_id)=2. As you've GROUP BY user_id the count of them represents that the number of field matches.