Saqueib Saqueib - 6 months ago 27
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 person.id, first_name, last_name, emails.email
FROM `person_has_emails`
LEFT JOIN person ON person.id = person_has_emails.person_id
LEFT JOIN emails ON person_has_emails.emails_id = 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

One way to do this would be:

SELECT A.first_name, A.last_name, C.email, E.phone FROM person A
LEFT JOIN person_has_emails B on B.person_id = A.id
LEFT JOIN emails C ON B.emails_id = C.id
LEFT JOIN person_has_phone D ON D.person_id = A.id
LEFT JOIN phone E ON D.phone_id = E.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?

Comments