Ttykuu

I have the following tables in my database:

people: phones:
+----+------+ +-----------+------------+-------------+
| id | name | | person_id | number | description |
+----+------+ +-----------+------------+-------------+
| 1 | Jade | | 1 | 1234567890 | home |
| 2 | Ben | | 1 | 987654321 | office |
+----+------+ +-----------+------------+-------------+

I use a query with INNER JOIN to select the name and phone numbers for each person:

SELECT
 id,
 name, 
 group_concat(
 concat(
 number,
 ' (',
 description,
 ')'
 ) ORDER BY description SEPARATOR ','
 ) AS phones
FROM people INNER JOIN phones
ON person_id = id
GROUP BY id;

Results from the above query:

+----+------+--------------------------------------+
| id | name | phones |
+----+------+--------------------------------------+
| 1 | Jade | 1234567890 (home),987654321 (office) |
+----+------+--------------------------------------+

But I want to use this query to go through all the people in my database and not just the ones with phone numbers, so I added the following row to the phones table:

INSERT INTO phones VALUES (0, 0, NULL);

And then modified the ON statement of the query to:

ON person_id = id OR person_id = 0

That way every person gets selected even if he has no phone numbers registered in the database:

+----+------+--------------------------------------+
| id | name | phones |
+----+------+--------------------------------------+
| 1 | Jade | 1234567890 (home),987654321 (office) |
| 2 | Ben | NULL |
+----+------+--------------------------------------+

Is this a proper way to achieve this? I don't really like the idea of having to make sure that the 0, 0, NULL row will always stay in the database in order to ensure the queries will work as intended. (since the company I develop this for might decide to clear the database)

This implementation requires calling an ensureNullRow() function every time before using this query, is this a good solution or would you do it differently?

What you are describing is known asLEFT OUTER JOIN

SELECT
 id,
 name, 
 group_concat(
 concat(
 number,
 ' (',
 description,
 ')'
 ) ORDER BY description SEPARATOR ','
 ) AS phones
FROM people LEFT OUTER JOIN phones
ON person_id = id
GROUP BY id;

inner join => give me only rows with records in both tables
left outer join => give me all rows of from the left table along with any matching rows in the right..