SQl server duplicate joins issue with different elements

up vote
-1
down vote

favorite

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 at 16:31

user3583912

830917

What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 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 at 16:36

@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 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 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 at 16:56

|
show 7 more comments

up vote
-1
down vote

favorite

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 at 16:31

user3583912

830917

What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 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 at 16:36

@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 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 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 at 16:56

|
show 7 more comments

up vote
-1
down vote

favorite

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 at 16:31

user3583912

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)

sql-server tsql sql-server-2012

asked Nov 21 at 16:31

user3583912

830917

asked Nov 21 at 16:31

user3583912

830917

asked Nov 21 at 16:31

user3583912

830917

asked Nov 21 at 16:31

user3583912

830917

asked Nov 21 at 16:31

user3583912

830917

What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 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 at 16:36

@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 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 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 at 16:56

|
show 7 more comments

What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 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 at 16:36

@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 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 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 at 16:56

What did you try and what was wrong with the result?
– Tab Alleman
Nov 21 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 at 16:36

@Larnu, I know but I got different requirement now. sorry about that
– user3583912
Nov 21 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 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 at 16:56

|
show 7 more comments

1 Answer
1

active

oldest

votes

up vote
0
down vote

--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

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

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',
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');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

--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

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

add a comment |

up vote
0
down vote

--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

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

add a comment |

up vote
0
down vote

--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

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

--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

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

edited Nov 21 at 17:01

answered Nov 21 at 16:53

Cato

2,421210

answered Nov 21 at 16:53

Cato

2,421210

answered Nov 21 at 16:53

Cato

2,421210

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.

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl