JOIN when row doesn't exist?
I have 3 tables (budget, transactions and categories).
Transactions can be categorised with a category_id (which links to the categories table where the category_name is store).
I'm selecting the transactions for a certain month and I need to join the category_id in the transactions table to the category_id in the categories table in order to get the category name for the transaction).
The problem is that some months don't have spending in a particular category, so when I join – it just doesn't select these transactions, but I'd still like to show them as 0 if the category isn't present for any transactions for that month (because it needs to be displayed on the UI as £0 of budget spent for that month).
I've tried using outer, left, outer left joins on the transactions > budgets join but I'm still not able to select the transactions if the category_id doesn't exist.
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period
from budgets
inner join categories on budgets.category_id = categories.category_id
inner join transactions on budgets.category_id = transactions.category_id
where month(date) = month("2019-01-15") and budgets.account_id = 5
group by budget_id
mysql
edited Jan 20 at 12:08
Nick
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
add a comment |
I have 3 tables (budget, transactions and categories).
Transactions can be categorised with a category_id (which links to the categories table where the category_name is store).
I'm selecting the transactions for a certain month and I need to join the category_id in the transactions table to the category_id in the categories table in order to get the category name for the transaction).
The problem is that some months don't have spending in a particular category, so when I join – it just doesn't select these transactions, but I'd still like to show them as 0 if the category isn't present for any transactions for that month (because it needs to be displayed on the UI as £0 of budget spent for that month).
I've tried using outer, left, outer left joins on the transactions > budgets join but I'm still not able to select the transactions if the category_id doesn't exist.
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period
from budgets
inner join categories on budgets.category_id = categories.category_id
inner join transactions on budgets.category_id = transactions.category_id
where month(date) = month("2019-01-15") and budgets.account_id = 5
group by budget_id
mysql
edited Jan 20 at 12:08
Nick
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
add a comment |
I have 3 tables (budget, transactions and categories).
Transactions can be categorised with a category_id (which links to the categories table where the category_name is store).
I'm selecting the transactions for a certain month and I need to join the category_id in the transactions table to the category_id in the categories table in order to get the category name for the transaction).
The problem is that some months don't have spending in a particular category, so when I join – it just doesn't select these transactions, but I'd still like to show them as 0 if the category isn't present for any transactions for that month (because it needs to be displayed on the UI as £0 of budget spent for that month).
I've tried using outer, left, outer left joins on the transactions > budgets join but I'm still not able to select the transactions if the category_id doesn't exist.
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period
from budgets
inner join categories on budgets.category_id = categories.category_id
inner join transactions on budgets.category_id = transactions.category_id
where month(date) = month("2019-01-15") and budgets.account_id = 5
group by budget_id
mysql
edited Jan 20 at 12:08
Nick
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
I have 3 tables (budget, transactions and categories).
Transactions can be categorised with a category_id (which links to the categories table where the category_name is store).
I'm selecting the transactions for a certain month and I need to join the category_id in the transactions table to the category_id in the categories table in order to get the category name for the transaction).
The problem is that some months don't have spending in a particular category, so when I join – it just doesn't select these transactions, but I'd still like to show them as 0 if the category isn't present for any transactions for that month (because it needs to be displayed on the UI as £0 of budget spent for that month).
I've tried using outer, left, outer left joins on the transactions > budgets join but I'm still not able to select the transactions if the category_id doesn't exist.
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period
from budgets
inner join categories on budgets.category_id = categories.category_id
inner join transactions on budgets.category_id = transactions.category_id
where month(date) = month("2019-01-15") and budgets.account_id = 5
group by budget_id
mysql
mysql
edited Jan 20 at 12:08
Nick
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
edited Jan 20 at 12:08
Nick
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
edited Jan 20 at 12:08
Nick
29.6k121941
edited Jan 20 at 12:08
Nick
29.6k121941
edited Jan 20 at 12:08
Nick
29.6k121941
29.6k121941
asked Jan 20 at 12:04
KyleKyle
31
asked Jan 20 at 12:04
KyleKyle
31
asked Jan 20 at 12:04
KyleKyle
31
31
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This will work:
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period,transactions.*
from budgets
left join categories on budgets.category_id = categories.category_id
left join transactions on transactions.category_id = budgets.category_id
group by budget_id
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
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
});
}
});
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%2f54276244%2fjoin-when-row-doesnt-exist%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
This will work:
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period,transactions.*
from budgets
left join categories on budgets.category_id = categories.category_id
left join transactions on transactions.category_id = budgets.category_id
group by budget_id
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
add a comment |
This will work:
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period,transactions.*
from budgets
left join categories on budgets.category_id = categories.category_id
left join transactions on transactions.category_id = budgets.category_id
group by budget_id
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
add a comment |
This will work:
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period,transactions.*
from budgets
left join categories on budgets.category_id = categories.category_id
left join transactions on transactions.category_id = budgets.category_id
group by budget_id
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
This will work:
select budget_id, category_name, total, sum(amount) as spent, total - sum(amount) as available, period,transactions.*
from budgets
left join categories on budgets.category_id = categories.category_id
left join transactions on transactions.category_id = budgets.category_id
group by budget_id
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
edited Jan 21 at 9:38
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
answered Jan 20 at 18:35
Richard SockerRichard Socker
1747
1747
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
add a comment |
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
It doesn't I'm afraid, if the category_id is set to null in the transactions table, it won't select it.
– Kyle
Jan 21 at 8:17
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
I edited my answer try this without "where month(date) = month("2019-01-15") and budgets.account_id = 5" .
– Richard Socker
Jan 21 at 9:43
add a comment |
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.
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%2f54276244%2fjoin-when-row-doesnt-exist%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
