There are three tables such as persons, emails, phones.

persons

+----+-----------+-----+ | id | firstname | age | +----+-----------+-----+ | 1 | Катя | 12 | | 2 | Лена | 18 | +----+-----------+-----+

email

 +----+-----------+-----------+ | id | email | person_id | +----+-----------+-----------+ | 1 | wer@sdf.ru| 1 | | 2 | | 2 | +----+-----------+-----------+

telephone

 +----+-----------+-----------+ | id | phone | person_id | +----+-----------+-----------+ | 1 | 3423434324| 1 | | 2 | | 2 | +----+-----------+-----------+

I make a request

 SELECT * FROM persons p INNER JOIN phones ph ON p.id = ph.person_id INNER JOIN emails e ON p.id = e.person_id`

In such cases, only Katya gets to the table on the site. Because she has both an email and a phone. How can I make a request for Lena to be in the table?

  • one
    And where is the aggregation? According to the given data, the query should work out normally, which means you are using one of the aggregation functions, which means you need to specify GROUP BY some_field in your case by the person_id field. Well, either there is no data in the tables for Lena - BOPOH

1 answer 1

In theory, you need to replace INNER JOIN with LEFT JOIN , in order to select even those people who do not have records with a phone or mail in the database.

By the way, judging by the above scheme and the problem described, you still have no entries in the phones or emails table with person_id=2 .

Most likely a working example:

 SELECT * FROM persons p LEFT JOIN phones ph ON p.id = ph.person_id LEFT JOIN emails e ON p.id = e.person_id

However, I do not recommend to write SELECT * right away for the future, it is better to always describe the required set of fields, for example select p.id, p.firstname, p.age, ph.phone, e.email .