MYSQL select query where multiple conditions in same column must exist

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 开发者_StackOverflow中文版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.

------- Adding more info:

1) Find any man in the USA that is between 22 and 53 that is personality type 2 (only) that is looking for a woman.

The sql will use these tables:

user desired_partner user_personality

The user table contains profile info that we want to grab from the database (user_id, desc, email, password, dob, gender_id, country_id, state_id, city, ethnicity_id)

In desired_partner table tells us what the user is looking for as a partner (i.e. male, female), primary key is desired_partner_id, and contains user_id, and gender_id

In the user_personality table, primary key is user_personality_id, and contains user_id and personality_id

user_personality table is needed because a user_id can be associated with different personality_id's.

personality_id can be used to look up personality_name in the personality table as defined: personality_id, personality_name

Here's the issue:

What if member has personality type 1 and personality type 4

This would be represented in the database as:

user_personality table user_personality_id = 1 user_id = 1 personality_id = 1 user_personality_id = 2 user_id = 1 personality_id = 4

If I wanted to find people that had personality type 1 -only- then the query below wouldn't work because it will include user_id = 1 who also has personality type 4.

select * from user, user_personality where user.user_id = user_personality.user_id and user_personality.personality_id = 1

Now, say a member wants to do a search for man that has personality type 1 and and personality type 5 and no other personality_id's associated with their user_id. What would the sql look like?

First question:

Are you sure your original query returns also other personality type? Because you are explicitly asking for type 2.
I cannot see how this should return other personalities. (So this would already be the answer to your first question).

Second question:

Use the IN operator:

AND user_personality.personality_id IN ('1', '2', '5')

If this does not help, you should explain your table structure better so that we know what kind of relationships exists between them.

A solution for question #1

AND NOT EXISTS ( SELECT 1
                   FROM user_personality UP2
                  WHERE UP2.user_id = user.user_id
                    AND UP2.personality_id <> '2' 
               )

By using NOT IN sub-selects are typically big hit performance wise. By doing an additional LEFT-JOIN to the user personality on anything BUT Personality ID = 2. If any found, they have multiple personality traits, and ignore them. Only keep if NULL is found in "UserP2" alias

SELECT 
      U.*
   FROM 
      `user` U
          JOIN `user_personality` UserP
             ON U.User_ID = UserP.User_ID
             AND UserP.Personality_ID = '2'
          JOIN `user_personality` UserP2
             ON U.User_ID = UserP2.User_ID
             AND UserP2.Personality_ID != '2'
          JOIN `desired_partner` DP
             ON U.User_ID = DP.User_ID
             AND DP.Gender_ID = '2'
   WHERE
          U.Country_ID = '190'
      AND U.Gender_ID = '1'
      AND U.DOB BETWEEN '1957-05-18' AND '1988-05-18'
      AND UserP2.User_ID IS NULL

For the second, just use an "IN" clause of the types you want... Unless you wanted to make sure the user had ALL 3 personality traits.

SELECT 
      U.*
   FROM 
      `user` U
          JOIN `user_personality` UserP
             ON U.User_ID = UserP.User_ID
             AND UserP.Personality_ID IN ( '1', '2', '5' )
          JOIN `desired_partner` DP
             ON U.User_ID = DP.User_ID
             AND DP.Gender_ID = '2'
   WHERE
          U.Country_ID = '190'
      AND U.Gender_ID = '1'
      AND U.DOB BETWEEN '1957-05-18' AND '1988-05-18'