Saqueib Saqueib - 2 years ago 109
SQL Question

Multi emails and phone database query MySQL

I wanted to store contacts in MySQL where one person can have many emails and phone numbers, I have created below diagram.

Please help me with the best query to get all emails and phones contacts of person.

However, if this schema is not good please advise me of a better schema.

I am using below query which is giving me emails:

SELECT, first_name, last_name,
FROM `person_has_emails`
LEFT JOIN person ON = person_has_emails.person_id
LEFT JOIN emails ON person_has_emails.emails_id =
LIMIT 0 , 30

However, I want first_name, last_name, email, phone_number returned from a single query. Please advise.

MySQL simple database schema

Answer Source

One way to do this would be:

SELECT A.first_name, A.last_name,, FROM person A
LEFT JOIN person_has_emails B on B.person_id =
LEFT JOIN emails C ON B.emails_id =
LEFT JOIN person_has_phone D ON D.person_id =
LEFT JOIN phone E ON D.phone_id =

But this will produce overhead. Emails will be paired with phone numbers, so if you have a person with 3 emails and 2 phone numbers you will have 6 rows in total for that same person.

Now, moving on to schema - you should remove the person_has emails and person_has_phone tables and link emails and phones directly to person. That is because one email or phone can not belong to multiple people. Or can it?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download