Mixing
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 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
|
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 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 '18 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 '18 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 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 '18 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 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 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
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 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
asked Nov 20 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
edited Nov 21 '18 at 11:33
Israel Ortuño
asked Nov 20 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
asked Nov 20 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
asked Nov 20 '18 at 22:58
Israel OrtuñoIsrael Ortuño
80521031
80521031
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 '18 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 '18 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 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 '18 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 at 11:33
|
show 4 more comments
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 '18 at 23:04
If you want to get rows of 4 values as you show you will need to useUNION
– Nick
Nov 20 '18 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 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 '18 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 at 11:33
1
1
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 '18 at 23:04
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Nov 20 '18 at 23:04
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 '18 at 23:05
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 '18 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 at 23:12
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 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 '18 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 '18 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 at 11:33
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 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 '18 at 23:17
RhythmRhythm
2736
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 '18 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 '18 at 23:17
RhythmRhythm
2736
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 '18 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 '18 at 23:17
RhythmRhythm
2736
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 '18 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 '18 at 23:17
RhythmRhythm
2736
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 '18 at 23:17
RhythmRhythm
2736
answered Nov 20 '18 at 23:17
RhythmRhythm
2736
answered Nov 20 '18 at 23:17
RhythmRhythm
2736
answered Nov 20 '18 at 23:17
RhythmRhythm
2736
2736
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 '18 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 '18 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 '18 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 '18 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.
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 '18 at 23:04
If you want to get rows of 4 values as you show you will need to use
UNION– Nick
Nov 20 '18 at 23:05
I updated the question, I hope is clearer now
– Israel Ortuño
Nov 20 '18 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 '18 at 5:07
@MadhurBhaiya thanks for the tip :) I have added how the original table looks like
– Israel Ortuño
Nov 21 '18 at 11:33