MySQL one-to-many query

Question

I have 3 tables, something like this (simplified ofc here):

• Users
  • user_id
  • user_name
• Information
  • info_id
  • user_id
  • speed
• contacts
  • contact_id
  • user_id
  • contact_data li>

users have a one-to-one relationship with information, although information does not always have an associated record.

users have a one-to-many relationship with contacts, although contacts do not always have related records.

I know that I can capture the proper “user information” + “information” with a left connection, is there a way to get all the data I want right away?

For example, one returned record could be:

user_id: 5
user_name: tom
info_id: 1
rate: 25.00
contact_id: 7
contact_data: 555-1212
contact_id: 8
contact_data: 555-1315
contact_id: 9
contact_data: 555-5511

Is this possible with a single request? Or should I use several?

Answer 1

, , .

, , :

SELECT
    u.user_id as user_id,
    u.user_name as user_name,
    i.info_id as info_id,
    i.rate as rate,
    c.contact_id as contact_id,
    c.contact_data as contact_data
FROM users as u
LEFT JOIN info as i ON i.user_id = u.user_id
LEFT JOIN contacts as c ON c.user_id = u.user_id

Answer 2

, , , , SQL , , , , .

:

1. , :

5 tom 1 25.00 7 555-1212
5 tom 1 25.00 8 555-1315
5 tom 1 25.00 9 555-5511

, , , , , , , . .

2. . , .

.

. . , . , , .