Mysql query to fetch records which are not matched form both tables
0
I am new to here.
I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary table and need to display to user if there is any difference between two tables like old value - new value. For this I tried below query but not able get the old value. I am getting as null
Temporay table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |2 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138875| 30059320 |25 |
+------+--------------+----------------+--+--+
Main Table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |5 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138888| 30059320 |25 |
+------+--------------+----------------+--+--+
From both tables I have change in ean field and in quantity field
For this I am using this query
SELECT
t2.id,t2.ean,t2.belegnr,t2.`quantity`,t1.`quantity` FROM
temp_table t2
LEFT OUTER JOIN details t1 ON
t1.asn = t2.asn
AND t1.ean = t2.ean
AND t1.belegnr = t2.belegnr
AND t1.quantity = t2.quantity
WHERE t2.asn = 700000845
AND t2.`belegnr` in(30059314,30059320)
AND t1.id IS NULL
Below is the my expected result
+------+--------------+----------------+-----+----------------------
| asn | ean | oldean| belegnr |quantity|Oldqty|
+------+--------------+----------------+-----+----------------------|
| 700000845 | 4046228201122| 4046228201122 |30059314 |2 | 5 |
| 700000845 | 4046228138875| 4046228138888 |30059320 |25 | 25 |
+------+--------------+----------------+--+--+--------+-------------+
Any help would be greatly appreciated.
mysql join
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 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 |
0
I am new to here.
I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary table and need to display to user if there is any difference between two tables like old value - new value. For this I tried below query but not able get the old value. I am getting as null
Temporay table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |2 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138875| 30059320 |25 |
+------+--------------+----------------+--+--+
Main Table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |5 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138888| 30059320 |25 |
+------+--------------+----------------+--+--+
From both tables I have change in ean field and in quantity field
For this I am using this query
SELECT
t2.id,t2.ean,t2.belegnr,t2.`quantity`,t1.`quantity` FROM
temp_table t2
LEFT OUTER JOIN details t1 ON
t1.asn = t2.asn
AND t1.ean = t2.ean
AND t1.belegnr = t2.belegnr
AND t1.quantity = t2.quantity
WHERE t2.asn = 700000845
AND t2.`belegnr` in(30059314,30059320)
AND t1.id IS NULL
Below is the my expected result
+------+--------------+----------------+-----+----------------------
| asn | ean | oldean| belegnr |quantity|Oldqty|
+------+--------------+----------------+-----+----------------------|
| 700000845 | 4046228201122| 4046228201122 |30059314 |2 | 5 |
| 700000845 | 4046228138875| 4046228138888 |30059320 |25 | 25 |
+------+--------------+----------------+--+--+--------+-------------+
Any help would be greatly appreciated.
mysql join
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
IsasnthePRIMARY KEYin each table? Please provideSHOW CREATE TABLE.
– Rick James
11 secs ago
add a comment |
0
0
0
I am new to here.
I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary table and need to display to user if there is any difference between two tables like old value - new value. For this I tried below query but not able get the old value. I am getting as null
Temporay table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |2 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138875| 30059320 |25 |
+------+--------------+----------------+--+--+
Main Table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |5 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138888| 30059320 |25 |
+------+--------------+----------------+--+--+
From both tables I have change in ean field and in quantity field
For this I am using this query
SELECT
t2.id,t2.ean,t2.belegnr,t2.`quantity`,t1.`quantity` FROM
temp_table t2
LEFT OUTER JOIN details t1 ON
t1.asn = t2.asn
AND t1.ean = t2.ean
AND t1.belegnr = t2.belegnr
AND t1.quantity = t2.quantity
WHERE t2.asn = 700000845
AND t2.`belegnr` in(30059314,30059320)
AND t1.id IS NULL
Below is the my expected result
+------+--------------+----------------+-----+----------------------
| asn | ean | oldean| belegnr |quantity|Oldqty|
+------+--------------+----------------+-----+----------------------|
| 700000845 | 4046228201122| 4046228201122 |30059314 |2 | 5 |
| 700000845 | 4046228138875| 4046228138888 |30059320 |25 | 25 |
+------+--------------+----------------+--+--+--------+-------------+
Any help would be greatly appreciated.
mysql join
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am new to here.
I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary table and need to display to user if there is any difference between two tables like old value - new value. For this I tried below query but not able get the old value. I am getting as null
Temporay table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |2 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138875| 30059320 |25 |
+------+--------------+----------------+--+--+
Main Table
+------+--------------+----------------+-----+
| asn | ean | belegnr |quantity|
+------+--------------+----------------+-----+
| 700000845 | 4046228201122| 30059314 |5 |
| 700000845 | 4046228138879| 30059314 |3 |
| 700000845 | 4046228138888| 30059320 |25 |
+------+--------------+----------------+--+--+
From both tables I have change in ean field and in quantity field
For this I am using this query
SELECT
t2.id,t2.ean,t2.belegnr,t2.`quantity`,t1.`quantity` FROM
temp_table t2
LEFT OUTER JOIN details t1 ON
t1.asn = t2.asn
AND t1.ean = t2.ean
AND t1.belegnr = t2.belegnr
AND t1.quantity = t2.quantity
WHERE t2.asn = 700000845
AND t2.`belegnr` in(30059314,30059320)
AND t1.id IS NULL
Below is the my expected result
+------+--------------+----------------+-----+----------------------
| asn | ean | oldean| belegnr |quantity|Oldqty|
+------+--------------+----------------+-----+----------------------|
| 700000845 | 4046228201122| 4046228201122 |30059314 |2 | 5 |
| 700000845 | 4046228138875| 4046228138888 |30059320 |25 | 25 |
+------+--------------+----------------+--+--+--------+-------------+
Any help would be greatly appreciated.
mysql join
mysql join
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
user3408779user3408779
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
user3408779user3408779
101
asked 4 mins ago
user3408779user3408779
101
101
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
user3408779 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
IsasnthePRIMARY KEYin each table? Please provideSHOW CREATE TABLE.
– Rick James
11 secs ago
add a comment |
IsasnthePRIMARY KEYin each table? Please provideSHOW CREATE TABLE.
– Rick James
11 secs ago
Is
asn the PRIMARY KEY in each table? Please provide SHOW CREATE TABLE.– Rick James
11 secs ago
Is
asn the PRIMARY KEY in each table? Please provide SHOW CREATE TABLE.– Rick James
11 secs ago
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
});
}
});
user3408779 is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f228984%2fmysql-query-to-fetch-records-which-are-not-matched-form-both-tables%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
user3408779 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
user3408779 is a new contributor. Be nice, and check out our Code of Conduct.
user3408779 is a new contributor. Be nice, and check out our Code of Conduct.
user3408779 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.
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%2fdba.stackexchange.com%2fquestions%2f228984%2fmysql-query-to-fetch-records-which-are-not-matched-form-both-tables%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
Is
asnthePRIMARY KEYin each table? Please provideSHOW CREATE TABLE.– Rick James
11 secs ago