SQl server duplicate joins issue with different elements
-1
Sorry, I am posting again with one more requirement.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
DROP TABLE IF EXISTS #TestTable1
DROP TABLE IF EXISTS #TestTable2
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(N'123953',300.02,N'Extra Pay')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50))
insert into #TestTable2 ([No],[Value2],[Desc])
Values
(N'123953',200.02,N'Extra Pay')
,(N'123953',553.02,N'Basic Hours')
,(N'123953',446.67,N'Basic Hours')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
Desired Output:
[No],[Desc],[Value1],[Value2],[MatchResult]
,(N'123953',N'Extra Pay',300.02,200.02, False)
,(N'123953',N'Basic Hours',427.2,427.2, True)
,(N'123953',N'Basic Hours',106.8,106.8, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,NULL,NULL)
,(N'123953',N'Basic Hours',105.6,105.6, True)
sql-server tsql sql-server-2012 asked Nov 21 '18 at 16:31
user3583912user3583912
830917
|
show 7 more comments
-1
Sorry, I am posting again with one more requirement.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
DROP TABLE IF EXISTS #TestTable1
DROP TABLE IF EXISTS #TestTable2
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(N'123953',300.02,N'Extra Pay')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50))
insert into #TestTable2 ([No],[Value2],[Desc])
Values
(N'123953',200.02,N'Extra Pay')
,(N'123953',553.02,N'Basic Hours')
,(N'123953',446.67,N'Basic Hours')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
Desired Output:
[No],[Desc],[Value1],[Value2],[MatchResult]
,(N'123953',N'Extra Pay',300.02,200.02, False)
,(N'123953',N'Basic Hours',427.2,427.2, True)
,(N'123953',N'Basic Hours',106.8,106.8, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,NULL,NULL)
,(N'123953',N'Basic Hours',105.6,105.6, True)
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56
|
show 7 more comments
-1
-1
-1
Sorry, I am posting again with one more requirement.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
DROP TABLE IF EXISTS #TestTable1
DROP TABLE IF EXISTS #TestTable2
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(N'123953',300.02,N'Extra Pay')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50))
insert into #TestTable2 ([No],[Value2],[Desc])
Values
(N'123953',200.02,N'Extra Pay')
,(N'123953',553.02,N'Basic Hours')
,(N'123953',446.67,N'Basic Hours')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
Desired Output:
[No],[Desc],[Value1],[Value2],[MatchResult]
,(N'123953',N'Extra Pay',300.02,200.02, False)
,(N'123953',N'Basic Hours',427.2,427.2, True)
,(N'123953',N'Basic Hours',106.8,106.8, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,NULL,NULL)
,(N'123953',N'Basic Hours',105.6,105.6, True)
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
Sorry, I am posting again with one more requirement.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
DROP TABLE IF EXISTS #TestTable1
DROP TABLE IF EXISTS #TestTable2
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(N'123953',300.02,N'Extra Pay')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50))
insert into #TestTable2 ([No],[Value2],[Desc])
Values
(N'123953',200.02,N'Extra Pay')
,(N'123953',553.02,N'Basic Hours')
,(N'123953',446.67,N'Basic Hours')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
Desired Output:
[No],[Desc],[Value1],[Value2],[MatchResult]
,(N'123953',N'Extra Pay',300.02,200.02, False)
,(N'123953',N'Basic Hours',427.2,427.2, True)
,(N'123953',N'Basic Hours',106.8,106.8, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,NULL,NULL)
,(N'123953',N'Basic Hours',105.6,105.6, True)
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
asked Nov 21 '18 at 16:31
user3583912user3583912
830917
830917
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56
|
show 7 more comments
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56
|
show 7 more comments
1 Answer
1
active
oldest
votes
0
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
answered Nov 21 '18 at 16:53
CatoCato
2,614210
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53416582%2fsql-server-duplicate-joins-issue-with-different-elements%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
0
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
answered Nov 21 '18 at 16:53
CatoCato
2,614210
add a comment |
0
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
answered Nov 21 '18 at 16:53
CatoCato
2,614210
add a comment |
0
0
0
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
answered Nov 21 '18 at 16:53
CatoCato
2,614210
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
answered Nov 21 '18 at 16:53
CatoCato
2,614210
edited Nov 21 '18 at 17:01
answered Nov 21 '18 at 16:53
CatoCato
2,614210
answered Nov 21 '18 at 16:53
CatoCato
2,614210
answered Nov 21 '18 at 16:53
CatoCato
2,614210
2,614210
add a comment |
add a comment |
draft saved
draft discarded
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.
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%2fstackoverflow.com%2fquestions%2f53416582%2fsql-server-duplicate-joins-issue-with-different-elements%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
What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 '18 at 16:36
Previous post. What's wrong with the accepted answer in the previous question? Why haven't you included the code you are using? What have you tried/changed to get your new expected results? Stack overflow isn't a free coding and support service, please don't treat it like one. If you don't know what/how the code works then you shouldn't be using the code you've been supplied; it's your responsibility to support it not the volunteers here.
– Larnu
Nov 21 '18 at 16:36
@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 '18 at 16:39
The requirement changing is fine, but please do show your attempt to meet the new requirement; using the code you were previously supplied or your own full attempt.
– Larnu
Nov 21 '18 at 16:40
is your match on 300.02,200.02 a typo? Otherwise I'm not clear what constitutes a match/non match. Why would the 'spare' 213.6 then join to nothing for example?
– Cato
Nov 21 '18 at 16:56