Mixing
LEFT JOIN help in sql
I have to make a list of customer who do not have any invoice but have paid an invoice … maybe twice.
But with my code (stated below) it contains everything from the left join. However I only need the lines highlighted with green.
How should I make a table with only the 2 highlights?
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value
sql postgresql outer-join
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
add a comment |
I have to make a list of customer who do not have any invoice but have paid an invoice … maybe twice.
But with my code (stated below) it contains everything from the left join. However I only need the lines highlighted with green.
How should I make a table with only the 2 highlights?
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value
sql postgresql outer-join
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10
add a comment |
I have to make a list of customer who do not have any invoice but have paid an invoice … maybe twice.
But with my code (stated below) it contains everything from the left join. However I only need the lines highlighted with green.
How should I make a table with only the 2 highlights?
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value
sql postgresql outer-join
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
I have to make a list of customer who do not have any invoice but have paid an invoice … maybe twice.
But with my code (stated below) it contains everything from the left join. However I only need the lines highlighted with green.
How should I make a table with only the 2 highlights?
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value
sql postgresql outer-join
sql postgresql outer-join
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
edited Nov 19 '18 at 13:59
a_horse_with_no_name
292k46445540
292k46445540
asked Nov 19 '18 at 13:54
ddsaaaa
1
asked Nov 19 '18 at 13:54
ddsaaaa
1
asked Nov 19 '18 at 13:54
ddsaaaa
1
1
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10
add a comment |
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10
add a comment |
4 Answers
4
active
oldest
votes
There are two issues in your SQL. First, you need to join on Invoice number, not on value, as joining on value is pointless. Second, you need to only pick those payments where there are no corresponding debts, i.e. when you left-join, the table on the right has "null" in the joining column. The SQL would be something like this:
SELECT paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.InvoiceNumber = paymentsfrombank.InvoiceNumber
WHERE debtors.InvoiceNumber is NULL
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
add a comment |
You only want to select columns from paymentsfrombank. So why do you even join?
select invoice_number, customer, value from paymentsfrombank
except
select invoice_number, customer, value from debtors;
(This requires exact matches as in your example, i.e. same amount for the invoice/customer).
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
add a comment |
in mysql we usually have this way to flip the relation and extract the rows that dosen't have relation.
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value where debtors.value is null
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
add a comment |
You can use NOT EXISTS :
SELECT p.*
FROM paymentsfrombank p
WHERE NOT EXISTS (SELECT 1 FROM debtors d WHERE d.invoice_number = p.invoice_number);
However, the LEFT OUTER JOIN would also work if you add filtered with WHERE Clause to filtered out only missing customers that haven't any invoice information :
SELECT p.invoicenumber, p.customer, p.value
FROM paymentsfrombank P LEFT OUTER JOIN
debtors d
ON d.InvoiceNumber = p.InvoiceNumber
WHERE d.InvoiceNumber IS NULL;
Note : I have used table alias (p & d) that makes query to easier read & write.
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
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%2f53376148%2fleft-join-help-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are two issues in your SQL. First, you need to join on Invoice number, not on value, as joining on value is pointless. Second, you need to only pick those payments where there are no corresponding debts, i.e. when you left-join, the table on the right has "null" in the joining column. The SQL would be something like this:
SELECT paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.InvoiceNumber = paymentsfrombank.InvoiceNumber
WHERE debtors.InvoiceNumber is NULL
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
add a comment |
There are two issues in your SQL. First, you need to join on Invoice number, not on value, as joining on value is pointless. Second, you need to only pick those payments where there are no corresponding debts, i.e. when you left-join, the table on the right has "null" in the joining column. The SQL would be something like this:
SELECT paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.InvoiceNumber = paymentsfrombank.InvoiceNumber
WHERE debtors.InvoiceNumber is NULL
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
add a comment |
There are two issues in your SQL. First, you need to join on Invoice number, not on value, as joining on value is pointless. Second, you need to only pick those payments where there are no corresponding debts, i.e. when you left-join, the table on the right has "null" in the joining column. The SQL would be something like this:
SELECT paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.InvoiceNumber = paymentsfrombank.InvoiceNumber
WHERE debtors.InvoiceNumber is NULL
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
There are two issues in your SQL. First, you need to join on Invoice number, not on value, as joining on value is pointless. Second, you need to only pick those payments where there are no corresponding debts, i.e. when you left-join, the table on the right has "null" in the joining column. The SQL would be something like this:
SELECT paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.InvoiceNumber = paymentsfrombank.InvoiceNumber
WHERE debtors.InvoiceNumber is NULL
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
answered Nov 19 '18 at 14:00
Aleks G
42.2k18120190
42.2k18120190
add a comment |
add a comment |
You only want to select columns from paymentsfrombank. So why do you even join?
select invoice_number, customer, value from paymentsfrombank
except
select invoice_number, customer, value from debtors;
(This requires exact matches as in your example, i.e. same amount for the invoice/customer).
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
add a comment |
You only want to select columns from paymentsfrombank. So why do you even join?
select invoice_number, customer, value from paymentsfrombank
except
select invoice_number, customer, value from debtors;
(This requires exact matches as in your example, i.e. same amount for the invoice/customer).
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
add a comment |
You only want to select columns from paymentsfrombank. So why do you even join?
select invoice_number, customer, value from paymentsfrombank
except
select invoice_number, customer, value from debtors;
(This requires exact matches as in your example, i.e. same amount for the invoice/customer).
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
You only want to select columns from paymentsfrombank. So why do you even join?
select invoice_number, customer, value from paymentsfrombank
except
select invoice_number, customer, value from debtors;
(This requires exact matches as in your example, i.e. same amount for the invoice/customer).
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
answered Nov 19 '18 at 14:08
Thorsten Kettner
50.3k22542
50.3k22542
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
add a comment |
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
Thank you it worked as expected :)
– ddsaaaa
Nov 19 '18 at 14:11
add a comment |
in mysql we usually have this way to flip the relation and extract the rows that dosen't have relation.
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value where debtors.value is null
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
add a comment |
in mysql we usually have this way to flip the relation and extract the rows that dosen't have relation.
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value where debtors.value is null
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
add a comment |
in mysql we usually have this way to flip the relation and extract the rows that dosen't have relation.
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value where debtors.value is null
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
in mysql we usually have this way to flip the relation and extract the rows that dosen't have relation.
Select paymentsfrombank.invoicenumber,paymentsfrombank.customer,paymentsfrombank.value
FROM paymentsfrombank
LEFT OUTER JOIN debtors
ON debtors.value = paymentsfrombank.value where debtors.value is null
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
answered Nov 19 '18 at 14:15
Hakeem Nofal
3913
3913
add a comment |
add a comment |
You can use NOT EXISTS :
SELECT p.*
FROM paymentsfrombank p
WHERE NOT EXISTS (SELECT 1 FROM debtors d WHERE d.invoice_number = p.invoice_number);
However, the LEFT OUTER JOIN would also work if you add filtered with WHERE Clause to filtered out only missing customers that haven't any invoice information :
SELECT p.invoicenumber, p.customer, p.value
FROM paymentsfrombank P LEFT OUTER JOIN
debtors d
ON d.InvoiceNumber = p.InvoiceNumber
WHERE d.InvoiceNumber IS NULL;
Note : I have used table alias (p & d) that makes query to easier read & write.
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
add a comment |
You can use NOT EXISTS :
SELECT p.*
FROM paymentsfrombank p
WHERE NOT EXISTS (SELECT 1 FROM debtors d WHERE d.invoice_number = p.invoice_number);
However, the LEFT OUTER JOIN would also work if you add filtered with WHERE Clause to filtered out only missing customers that haven't any invoice information :
SELECT p.invoicenumber, p.customer, p.value
FROM paymentsfrombank P LEFT OUTER JOIN
debtors d
ON d.InvoiceNumber = p.InvoiceNumber
WHERE d.InvoiceNumber IS NULL;
Note : I have used table alias (p & d) that makes query to easier read & write.
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
add a comment |
You can use NOT EXISTS :
SELECT p.*
FROM paymentsfrombank p
WHERE NOT EXISTS (SELECT 1 FROM debtors d WHERE d.invoice_number = p.invoice_number);
However, the LEFT OUTER JOIN would also work if you add filtered with WHERE Clause to filtered out only missing customers that haven't any invoice information :
SELECT p.invoicenumber, p.customer, p.value
FROM paymentsfrombank P LEFT OUTER JOIN
debtors d
ON d.InvoiceNumber = p.InvoiceNumber
WHERE d.InvoiceNumber IS NULL;
Note : I have used table alias (p & d) that makes query to easier read & write.
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
You can use NOT EXISTS :
SELECT p.*
FROM paymentsfrombank p
WHERE NOT EXISTS (SELECT 1 FROM debtors d WHERE d.invoice_number = p.invoice_number);
However, the LEFT OUTER JOIN would also work if you add filtered with WHERE Clause to filtered out only missing customers that haven't any invoice information :
SELECT p.invoicenumber, p.customer, p.value
FROM paymentsfrombank P LEFT OUTER JOIN
debtors d
ON d.InvoiceNumber = p.InvoiceNumber
WHERE d.InvoiceNumber IS NULL;
Note : I have used table alias (p & d) that makes query to easier read & write.
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
edited Nov 19 '18 at 14:17
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
answered Nov 19 '18 at 14:12
Yogesh Sharma
28.2k51335
28.2k51335
add a comment |
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.
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.
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%2f53376148%2fleft-join-help-in-sql%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
You should probably join the tables on Invoice Number, not value.
– jchevali
Nov 19 '18 at 14:03
So there is always an exact match? An invoice has only one or zero records in each table? If an invoice exists in both tables, the customer and amount are exactly the same?
– Thorsten Kettner
Nov 19 '18 at 14:10