select returns zero rows although it should return some entries
I have a postgres database representing a simple demo forum. There are two tables called
topic
id | topic_id (the parent topic) | name | description
-------------------------------------------------------
uuid | uuid | text | text
and thread
id | topic_id | name | created_at
-------------------------------------------------------
uuid | uuid | text | timestamp without timezone
By using an Express REST API I want to get all the information for one topic. I pass in it's ID and would like to get a result like this
{
parentTopic: {}, // could be null if no parent is available
name: "",
description: "",
childTopics: [{}, {}, {}],
threads: [{}, {}, {}] // should be sorted by created_at
}
As far as I know it is not possible to return a result like this. But I tried to create a query to get these information and maybe I can map this later on with some code.
SELECT
currentTopic.name,
currentTopic.description,
parentTopic.id AS parentTopicId,
parentTopic.name AS parentTopicName,
parentTopic.description AS parentTopicDescription,
childTopic.id AS childTopicId,
childTopic.name AS childTopicName,
childTopic.description AS childTopicDescription,
linkedThread.id AS threadId,
linkedThread.name AS threadName,
linkedThread.created_at AS threadCreatedAt
FROM
topic currentTopic
INNER JOIN
topic parentTopic ON currentTopic.topic_id = parentTopic.id
INNER JOIN
topic childTopic ON currentTopic.id = childTopic.topic_id
INNER JOIN
thread linkedThread ON currentTopic.id = linkedThread.topic_id
WHERE
currentTopic.id = '624aaab6-2d2d-45dc-a425-c2863f05779c'
ORDER BY
linkedThread.created_at;
When executing this (I use pgAdmin) I no error occurs but I get 0 rows returned. I made sure, there should be some entries to retrieve.
Is something wrong with that query? Is there a way to improve the query to return me a result that is ready to use (the JS object I mentioned above)?
postgresql
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r 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 |
I have a postgres database representing a simple demo forum. There are two tables called
topic
id | topic_id (the parent topic) | name | description
-------------------------------------------------------
uuid | uuid | text | text
and thread
id | topic_id | name | created_at
-------------------------------------------------------
uuid | uuid | text | timestamp without timezone
By using an Express REST API I want to get all the information for one topic. I pass in it's ID and would like to get a result like this
{
parentTopic: {}, // could be null if no parent is available
name: "",
description: "",
childTopics: [{}, {}, {}],
threads: [{}, {}, {}] // should be sorted by created_at
}
As far as I know it is not possible to return a result like this. But I tried to create a query to get these information and maybe I can map this later on with some code.
SELECT
currentTopic.name,
currentTopic.description,
parentTopic.id AS parentTopicId,
parentTopic.name AS parentTopicName,
parentTopic.description AS parentTopicDescription,
childTopic.id AS childTopicId,
childTopic.name AS childTopicName,
childTopic.description AS childTopicDescription,
linkedThread.id AS threadId,
linkedThread.name AS threadName,
linkedThread.created_at AS threadCreatedAt
FROM
topic currentTopic
INNER JOIN
topic parentTopic ON currentTopic.topic_id = parentTopic.id
INNER JOIN
topic childTopic ON currentTopic.id = childTopic.topic_id
INNER JOIN
thread linkedThread ON currentTopic.id = linkedThread.topic_id
WHERE
currentTopic.id = '624aaab6-2d2d-45dc-a425-c2863f05779c'
ORDER BY
linkedThread.created_at;
When executing this (I use pgAdmin) I no error occurs but I get 0 rows returned. I made sure, there should be some entries to retrieve.
Is something wrong with that query? Is there a way to improve the query to return me a result that is ready to use (the JS object I mentioned above)?
postgresql
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r 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 |
I have a postgres database representing a simple demo forum. There are two tables called
topic
id | topic_id (the parent topic) | name | description
-------------------------------------------------------
uuid | uuid | text | text
and thread
id | topic_id | name | created_at
-------------------------------------------------------
uuid | uuid | text | timestamp without timezone
By using an Express REST API I want to get all the information for one topic. I pass in it's ID and would like to get a result like this
{
parentTopic: {}, // could be null if no parent is available
name: "",
description: "",
childTopics: [{}, {}, {}],
threads: [{}, {}, {}] // should be sorted by created_at
}
As far as I know it is not possible to return a result like this. But I tried to create a query to get these information and maybe I can map this later on with some code.
SELECT
currentTopic.name,
currentTopic.description,
parentTopic.id AS parentTopicId,
parentTopic.name AS parentTopicName,
parentTopic.description AS parentTopicDescription,
childTopic.id AS childTopicId,
childTopic.name AS childTopicName,
childTopic.description AS childTopicDescription,
linkedThread.id AS threadId,
linkedThread.name AS threadName,
linkedThread.created_at AS threadCreatedAt
FROM
topic currentTopic
INNER JOIN
topic parentTopic ON currentTopic.topic_id = parentTopic.id
INNER JOIN
topic childTopic ON currentTopic.id = childTopic.topic_id
INNER JOIN
thread linkedThread ON currentTopic.id = linkedThread.topic_id
WHERE
currentTopic.id = '624aaab6-2d2d-45dc-a425-c2863f05779c'
ORDER BY
linkedThread.created_at;
When executing this (I use pgAdmin) I no error occurs but I get 0 rows returned. I made sure, there should be some entries to retrieve.
Is something wrong with that query? Is there a way to improve the query to return me a result that is ready to use (the JS object I mentioned above)?
postgresql
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a postgres database representing a simple demo forum. There are two tables called
topic
id | topic_id (the parent topic) | name | description
-------------------------------------------------------
uuid | uuid | text | text
and thread
id | topic_id | name | created_at
-------------------------------------------------------
uuid | uuid | text | timestamp without timezone
By using an Express REST API I want to get all the information for one topic. I pass in it's ID and would like to get a result like this
{
parentTopic: {}, // could be null if no parent is available
name: "",
description: "",
childTopics: [{}, {}, {}],
threads: [{}, {}, {}] // should be sorted by created_at
}
As far as I know it is not possible to return a result like this. But I tried to create a query to get these information and maybe I can map this later on with some code.
SELECT
currentTopic.name,
currentTopic.description,
parentTopic.id AS parentTopicId,
parentTopic.name AS parentTopicName,
parentTopic.description AS parentTopicDescription,
childTopic.id AS childTopicId,
childTopic.name AS childTopicName,
childTopic.description AS childTopicDescription,
linkedThread.id AS threadId,
linkedThread.name AS threadName,
linkedThread.created_at AS threadCreatedAt
FROM
topic currentTopic
INNER JOIN
topic parentTopic ON currentTopic.topic_id = parentTopic.id
INNER JOIN
topic childTopic ON currentTopic.id = childTopic.topic_id
INNER JOIN
thread linkedThread ON currentTopic.id = linkedThread.topic_id
WHERE
currentTopic.id = '624aaab6-2d2d-45dc-a425-c2863f05779c'
ORDER BY
linkedThread.created_at;
When executing this (I use pgAdmin) I no error occurs but I get 0 rows returned. I made sure, there should be some entries to retrieve.
Is something wrong with that query? Is there a way to improve the query to return me a result that is ready to use (the JS object I mentioned above)?
postgresql
postgresql
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 mins ago
Question3rQuestion3r
101
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 mins ago
Question3rQuestion3r
101
asked 10 mins ago
Question3rQuestion3r
101
101
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Question3r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Question3r 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 |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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
});
}
});
Question3r is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f232340%2fselect-returns-zero-rows-although-it-should-return-some-entries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Question3r is a new contributor. Be nice, and check out our Code of Conduct.
Question3r is a new contributor. Be nice, and check out our Code of Conduct.
Question3r is a new contributor. Be nice, and check out our Code of Conduct.
Question3r is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators 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.
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%2fdba.stackexchange.com%2fquestions%2f232340%2fselect-returns-zero-rows-although-it-should-return-some-entries%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
