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
custom_fields as f1 on f1.user_id = login_table.user_id 
custom_fields as f2 on f2.user_id = login_table.user_id 
f1.custom_name = '1' and f1.custom_value in (1,2,3)
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

enter image description here

Any advice? Thanks in advance.

  • Can you please apply some formatting so your question/query/result is readable? – danblack Nov 19 '18 at 7:29
  • Sure, its by mistake.one moment please – Sameer Nov 19 '18 at 7:30

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.

  • Yes Dan, but I need to eliminate extra joins to same table but keeping correct data.Is that possible. – Sameer Nov 19 '18 at 8:30
  • It is as simple as possible. EAV schemas do add a lot of joins. You do need one for each field name. – danblack Nov 19 '18 at 8:54
  • Thanks @danblack, your EAV concept solved my most of the issues. – Sameer Jul 2 at 10:23

(I don't understand your use of UNION; I'll focus on the question.)

FROM `deleted-login_details` AS login
JOIN custom_fields AS cf ON cf.user_id = login.user_id
WHERE IF(name = '1', value IN (1,2,3),
                     value IN (4,5,6))

If you need more than 2 choices, then consider the CASE .. WHEN .. END syntax.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.