SQL to fetch data where Unique key matches but the data is different in some other columns between different...
I have two tables of same structure as below. I am trying to write a query to compare both the tables using the Unique key which is the first column and trying to return values when there is a mismatch in the second column.
If the key is not present then no need to consider that data. only if the key is present in both the table then we have compare it.
Table A
ColumnA ColumnB
A 1
B 2
C 2
D 8
Table B
ColumnC ColumnD
A 1
B 3
C 5
F 4
For example the output of the above table when comparing Table A with B should be
B 2
C 2
and when comparing Table B with A it should be
B 3
C 5
Ideally the difference in the base table should come.
I have tried Joins and Unions but I am not able to fetch the data as mentioned above.
mysql sql
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
add a comment |
I have two tables of same structure as below. I am trying to write a query to compare both the tables using the Unique key which is the first column and trying to return values when there is a mismatch in the second column.
If the key is not present then no need to consider that data. only if the key is present in both the table then we have compare it.
Table A
ColumnA ColumnB
A 1
B 2
C 2
D 8
Table B
ColumnC ColumnD
A 1
B 3
C 5
F 4
For example the output of the above table when comparing Table A with B should be
B 2
C 2
and when comparing Table B with A it should be
B 3
C 5
Ideally the difference in the base table should come.
I have tried Joins and Unions but I am not able to fetch the data as mentioned above.
mysql sql
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
Hint:INNER JOIN.
– Gordon Linoff
Nov 14 '18 at 13:12
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58
add a comment |
I have two tables of same structure as below. I am trying to write a query to compare both the tables using the Unique key which is the first column and trying to return values when there is a mismatch in the second column.
If the key is not present then no need to consider that data. only if the key is present in both the table then we have compare it.
Table A
ColumnA ColumnB
A 1
B 2
C 2
D 8
Table B
ColumnC ColumnD
A 1
B 3
C 5
F 4
For example the output of the above table when comparing Table A with B should be
B 2
C 2
and when comparing Table B with A it should be
B 3
C 5
Ideally the difference in the base table should come.
I have tried Joins and Unions but I am not able to fetch the data as mentioned above.
mysql sql
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
I have two tables of same structure as below. I am trying to write a query to compare both the tables using the Unique key which is the first column and trying to return values when there is a mismatch in the second column.
If the key is not present then no need to consider that data. only if the key is present in both the table then we have compare it.
Table A
ColumnA ColumnB
A 1
B 2
C 2
D 8
Table B
ColumnC ColumnD
A 1
B 3
C 5
F 4
For example the output of the above table when comparing Table A with B should be
B 2
C 2
and when comparing Table B with A it should be
B 3
C 5
Ideally the difference in the base table should come.
I have tried Joins and Unions but I am not able to fetch the data as mentioned above.
mysql sql
mysql sql
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
asked Nov 14 '18 at 13:06
Sekar RamuSekar Ramu
16419
16419
Hint:INNER JOIN.
– Gordon Linoff
Nov 14 '18 at 13:12
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58
add a comment |
Hint:INNER JOIN.
– Gordon Linoff
Nov 14 '18 at 13:12
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58
Hint:
INNER JOIN.– Gordon Linoff
Nov 14 '18 at 13:12
Hint:
INNER JOIN.– Gordon Linoff
Nov 14 '18 at 13:12
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58
add a comment |
3 Answers
3
active
oldest
votes
- Since you want only those rows which has matching FK values in both the tables, we simply need to use
INNER JOIN. - Now, we can simply consider the unmatching rows by using
WHERE .. <> ..
When comparing Table A against Table B, we can get Table A rows only:
SELECT
tA.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
When comparing Table B against Table A, simply fetch the rows from Table B only:
SELECT
tB.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
I would do :
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);
Same would be for second version just need to swipe the table names.
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
add a comment |
use EXISTS and union all
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
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%2f53300948%2fsql-to-fetch-data-where-unique-key-matches-but-the-data-is-different-in-some-oth%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
- Since you want only those rows which has matching FK values in both the tables, we simply need to use
INNER JOIN. - Now, we can simply consider the unmatching rows by using
WHERE .. <> ..
When comparing Table A against Table B, we can get Table A rows only:
SELECT
tA.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
When comparing Table B against Table A, simply fetch the rows from Table B only:
SELECT
tB.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
- Since you want only those rows which has matching FK values in both the tables, we simply need to use
INNER JOIN. - Now, we can simply consider the unmatching rows by using
WHERE .. <> ..
When comparing Table A against Table B, we can get Table A rows only:
SELECT
tA.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
When comparing Table B against Table A, simply fetch the rows from Table B only:
SELECT
tB.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
- Since you want only those rows which has matching FK values in both the tables, we simply need to use
INNER JOIN. - Now, we can simply consider the unmatching rows by using
WHERE .. <> ..
When comparing Table A against Table B, we can get Table A rows only:
SELECT
tA.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
When comparing Table B against Table A, simply fetch the rows from Table B only:
SELECT
tB.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
- Since you want only those rows which has matching FK values in both the tables, we simply need to use
INNER JOIN. - Now, we can simply consider the unmatching rows by using
WHERE .. <> ..
When comparing Table A against Table B, we can get Table A rows only:
SELECT
tA.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
When comparing Table B against Table A, simply fetch the rows from Table B only:
SELECT
tB.*
FROM tableA AS tA
JOIN tableB AS tB
ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
edited Nov 14 '18 at 13:13
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
answered Nov 14 '18 at 13:08
Madhur BhaiyaMadhur Bhaiya
19.5k62236
19.5k62236
add a comment |
add a comment |
I would do :
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);
Same would be for second version just need to swipe the table names.
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
add a comment |
I would do :
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);
Same would be for second version just need to swipe the table names.
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
add a comment |
I would do :
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);
Same would be for second version just need to swipe the table names.
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
I would do :
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);
Same would be for second version just need to swipe the table names.
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 14 '18 at 13:09
Yogesh SharmaYogesh Sharma
30.9k51437
30.9k51437
add a comment |
add a comment |
use EXISTS and union all
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
add a comment |
use EXISTS and union all
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
add a comment |
use EXISTS and union all
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
use EXISTS and union all
SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
answered Nov 14 '18 at 13:11
Zaynul Abadin TuhinZaynul Abadin Tuhin
13.3k21032
13.3k21032
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.
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%2f53300948%2fsql-to-fetch-data-where-unique-key-matches-but-the-data-is-different-in-some-oth%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
Hint:
INNER JOIN.– Gordon Linoff
Nov 14 '18 at 13:12
Thanks @GordonLinoff !!! Confused myself with implementing Unions along with Joins. Simple joins helped me.
– Sekar Ramu
Nov 15 '18 at 5:58