Listing people with their phone numbers (if any)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
$begingroup$
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?
sql join
edited 10 mins ago
200_success
131k17157422
asked 12 hours ago
potatopotato
29710
$endgroup$
add a comment |
$begingroup$
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?
sql join
edited 10 mins ago
200_success
131k17157422
asked 12 hours ago
potatopotato
29710
$endgroup$
add a comment |
$begingroup$
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?
sql join
edited 10 mins ago
200_success
131k17157422
asked 12 hours ago
potatopotato
29710
$endgroup$
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?
sql join
sql join
edited 10 mins ago
200_success
131k17157422
asked 12 hours ago
potatopotato
29710
edited 10 mins ago
200_success
131k17157422
asked 12 hours ago
potatopotato
29710
edited 10 mins ago
200_success
131k17157422
edited 10 mins ago
200_success
131k17157422
edited 10 mins ago
200_success
131k17157422
131k17157422
asked 12 hours ago
potatopotato
29710
asked 12 hours ago
potatopotato
29710
asked 12 hours ago
potatopotato
29710
29710
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
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 tablesleft outer join => give me all rows of from the left table along with any matching rows in the right..
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f216927%2flisting-people-with-their-phone-numbers-if-any%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$begingroup$
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 tablesleft outer join => give me all rows of from the left table along with any matching rows in the right..
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
$begingroup$
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 tablesleft outer join => give me all rows of from the left table along with any matching rows in the right..
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
$begingroup$
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 tablesleft outer join => give me all rows of from the left table along with any matching rows in the right..
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
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 tablesleft outer join => give me all rows of from the left table along with any matching rows in the right..
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 hours ago
CharlesCharles
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 hours ago
CharlesCharles
1363
answered 12 hours ago
CharlesCharles
1363
1363
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Charles is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f216927%2flisting-people-with-their-phone-numbers-if-any%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
