MYSQL select query where multiple conditions in same column must exist
- by David
I'm putting together a dating site and I'm having a mysql query issue.
This works:
SELECT *
FROM `user`
, `desired_partner`
, `user_personality`
WHERE dob BETWEEN '1957-05-18' AND '1988-05-18'
AND country_id = '190'
AND user.gender_id = '1'
AND user.user_id = desired_partner.user_id
AND desired_partner.gender_id = '2'
AND user.user_id = user_personality.user_id
AND user_personality.personality_id = '2'
The sql finds any male (gender_id=1) with ATLEAST personality trait 2 (and possibly other personality traits) between certain age range in the USA (country_id=190) looking for a female (gender_id=2).
Question 1) How do I make it so it returns those with personality type 2 ONLY and no other personality traits?
Find any man in the USA that is between 22 and 53 that is of personality type 2 (only) that is looking for a woman.
Question 2) Supposing I want to find someone that matches personality type 1, personality type 2, and personality type 5 ONLY. There are 14 personality traits in the database and a user can be associated with any of them.
Find any man in the USA that is between 22 and 53 that is of personality type 1, 2, and 5 (ONLY) that is looking for a woman.