MySQL search problem

[Shawazi]

I have a table of users, and a table of e-mails. A user can have any number of e-mails but only one is designated as primary.

users

29 | Dave Smith

user_emails

id | user_id | email | primary
1 | 29 | [e-mail] | 0
2 | 29 | [e-mail] | 1
3 | 29 | [e-mail] | 0

What I want is that if I search "dave smith", it will return one item instead of three. Basically it would spit out Dave Smith from [e-mail] - since the gmail is designated as primary. I'm not even sure if that's possible in one query.

[NogDog]

I'm guessing at some column names, so adjust as needed:

SELECT user_emails.email FROM user_emails
INNER JOIN users ON user_emails.user_id = users.user_id
WHERE users.user_name = 'Dave Smith' AND user_emails.primary = 1

Also, depending on exactly what you are doing, you may need to change the name comparison to deal with upper/lower case letters.

[Shawazi]

Thanks for the answer. It was pretty easy, I don't know where my frame of mind got lost. I think it's because I was switching between searching by e-mail and name too much before a break 😉