Join self table and get values in new rows
-1
I wonder if there is a way to JOIN a self table and then get those values in new rows.
The original table looks similar to this:
| id | slug | parent_id |
|----|---------|-----------|
| 1 | one | NULL |
| 2 | two | 1 |
I dynamically generate this query from code which may contain N amount of JOIN statements:
SELECT * FROM `permalinks`
INNER JOIN `permalinks` AS `permalinks1`
ON `permalinks`.`id` = `permalinks1`.`parent_id`
AND `permalinks1`.`slug` = 'two'
WHERE `permalinks`.`slug` = 'one'
This produces:
| id | slug | id | slug |
|----|---------|----|--------|
| 1 | one | 2 | two |
However I would like to get:
| id | slug |
|----|---------|
| 1 | one |
| 2 | two |
Is it possible to get all new joining values as a new row?
mysql sql join inner-join
asked Nov 20 at 22:58
Israel Ortuño
7952930
|
show 4 more comments
-1
I wonder if there is a way to JOIN a self table and then get those values in new rows.
The original table looks similar to this:
| id | slug | parent_id |
|----|---------|-----------|
| 1 | one | NULL |
| 2 | two | 1 |
I dynamically generate this query from code which may contain N amount of JOIN statements:
SELECT * FROM `permalinks`
INNER JOIN `permalinks` AS `permalinks1`
ON `permalinks`.`id` = `permalinks1`.`parent_id`
AND `permalinks1`.`slug` = 'two'
WHERE `permalinks`.`slug` = 'one'
This produces:
| id | slug | id | slug |
|----|---------|----|--------|
| 1 | one | 2 | two |
However I would like to get:
| id | slug |
|----|---------|
| 1 | one |
| 2 | two |
Is it possible to get all new joining values as a new row?
mysql sql join inner-join
asked Nov 20 at 22:58
Israel Ortuño
7952930
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use@and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.
– Madhur Bhaiya
Nov 21 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33
|
show 4 more comments
-1
-1
-1
I wonder if there is a way to JOIN a self table and then get those values in new rows.
The original table looks similar to this:
| id | slug | parent_id |
|----|---------|-----------|
| 1 | one | NULL |
| 2 | two | 1 |
I dynamically generate this query from code which may contain N amount of JOIN statements:
SELECT * FROM `permalinks`
INNER JOIN `permalinks` AS `permalinks1`
ON `permalinks`.`id` = `permalinks1`.`parent_id`
AND `permalinks1`.`slug` = 'two'
WHERE `permalinks`.`slug` = 'one'
This produces:
| id | slug | id | slug |
|----|---------|----|--------|
| 1 | one | 2 | two |
However I would like to get:
| id | slug |
|----|---------|
| 1 | one |
| 2 | two |
Is it possible to get all new joining values as a new row?
mysql sql join inner-join
asked Nov 20 at 22:58
Israel Ortuño
7952930
I wonder if there is a way to JOIN a self table and then get those values in new rows.
The original table looks similar to this:
| id | slug | parent_id |
|----|---------|-----------|
| 1 | one | NULL |
| 2 | two | 1 |
I dynamically generate this query from code which may contain N amount of JOIN statements:
SELECT * FROM `permalinks`
INNER JOIN `permalinks` AS `permalinks1`
ON `permalinks`.`id` = `permalinks1`.`parent_id`
AND `permalinks1`.`slug` = 'two'
WHERE `permalinks`.`slug` = 'one'
This produces:
| id | slug | id | slug |
|----|---------|----|--------|
| 1 | one | 2 | two |
However I would like to get:
| id | slug |
|----|---------|
| 1 | one |
| 2 | two |
Is it possible to get all new joining values as a new row?
mysql sql join inner-join
mysql sql join inner-join
asked Nov 20 at 22:58
Israel Ortuño
7952930
asked Nov 20 at 22:58
Israel Ortuño
7952930
edited Nov 21 at 11:33
asked Nov 20 at 22:58
Israel Ortuño
7952930
asked Nov 20 at 22:58
Israel Ortuño
7952930
asked Nov 20 at 22:58
Israel Ortuño
7952930
7952930
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use@and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.
– Madhur Bhaiya
Nov 21 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33
|
show 4 more comments
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use@and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.
– Madhur Bhaiya
Nov 21 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33
1
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 at 23:05
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use
@ and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.– Madhur Bhaiya
Nov 21 at 5:07
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use
@ and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.– Madhur Bhaiya
Nov 21 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33
|
show 4 more comments
1 Answer
1
active
oldest
votes
0
It can be done by using the same query twice with minor tweaks and generating rows after union
SELECT perm.Id, perm.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one'
union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one';
answered Nov 20 at 23:17
Rhythm
2106
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
add a comment |
Your Answer
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: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53402863%2fjoin-self-table-and-get-values-in-new-rows%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
0
It can be done by using the same query twice with minor tweaks and generating rows after union
SELECT perm.Id, perm.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one'
union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one';
answered Nov 20 at 23:17
Rhythm
2106
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
add a comment |
0
It can be done by using the same query twice with minor tweaks and generating rows after union
SELECT perm.Id, perm.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one'
union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one';
answered Nov 20 at 23:17
Rhythm
2106
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
add a comment |
0
0
0
It can be done by using the same query twice with minor tweaks and generating rows after union
SELECT perm.Id, perm.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one'
union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one';
answered Nov 20 at 23:17
Rhythm
2106
It can be done by using the same query twice with minor tweaks and generating rows after union
SELECT perm.Id, perm.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one'
union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
INNER JOIN permalinks AS permalinks1
ON perm.id = permalinks1.parent_id
AND permalinks1.slug = 'two'
WHERE perm.slug = 'one';
answered Nov 20 at 23:17
Rhythm
2106
answered Nov 20 at 23:17
Rhythm
2106
answered Nov 20 at 23:17
Rhythm
2106
answered Nov 20 at 23:17
Rhythm
2106
2106
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
add a comment |
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
I feel weird using the same query over and over again, no way to convert a horizontal result to vertical?
– Israel Ortuño
Nov 21 at 11:36
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53402863%2fjoin-self-table-and-get-values-in-new-rows%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
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 at 23:04
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 at 23:12
@IsraelOrtuño how does the sample data in your table looks like ? Also, when you are replying to someone, please use
@and tag their names. Otherwise, most likely they will not receive a notification if you have clarified the question further.– Madhur Bhaiya
Nov 21 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 at 11:33