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?