SQl server duplicate joins issue
2
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(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',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]
(N'123953',N'Basic Hours',427.2,427.2)
,(N'123953',N'Basic Hours',106.8,106.8)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,NULL)
,(N'123953',N'Basic Hours',105.6,105.6)
Note: I can't join on Value field for other reasons.
I tried to use row_number() but still not coming as my desired output.
Thanks in advance.
sql-server tsql sql-server-2012 asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
|
show 6 more comments
2
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(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',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]
(N'123953',N'Basic Hours',427.2,427.2)
,(N'123953',N'Basic Hours',106.8,106.8)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,NULL)
,(N'123953',N'Basic Hours',105.6,105.6)
Note: I can't join on Value field for other reasons.
I tried to use row_number() but still not coming as my desired output.
Thanks in advance.
sql-server tsql sql-server-2012 asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
2
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't usevalue1 = value2in theJOIN, then what determines the relationship?).
– Larnu
Nov 21 '18 at 12:00
I can't join on Value field for other reasons Alright, then join those tables on what?T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?
– Sami
Nov 21 '18 at 12:03
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04
|
show 6 more comments
2
2
2
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(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',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]
(N'123953',N'Basic Hours',427.2,427.2)
,(N'123953',N'Basic Hours',106.8,106.8)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,NULL)
,(N'123953',N'Basic Hours',105.6,105.6)
Note: I can't join on Value field for other reasons.
I tried to use row_number() but still not coming as my desired output.
Thanks in advance.
sql-server tsql sql-server-2012 asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(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',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]
(N'123953',N'Basic Hours',427.2,427.2)
,(N'123953',N'Basic Hours',106.8,106.8)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,NULL)
,(N'123953',N'Basic Hours',105.6,105.6)
Note: I can't join on Value field for other reasons.
I tried to use row_number() but still not coming as my desired output.
Thanks in advance.
sql-server tsql sql-server-2012 sql-server tsql sql-server-2012 asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
asked Nov 21 '18 at 11:54
user3583912user3583912
8401017
8401017
2
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't usevalue1 = value2in theJOIN, then what determines the relationship?).
– Larnu
Nov 21 '18 at 12:00
I can't join on Value field for other reasons Alright, then join those tables on what?T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?
– Sami
Nov 21 '18 at 12:03
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04
|
show 6 more comments
2
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't usevalue1 = value2in theJOIN, then what determines the relationship?).
– Larnu
Nov 21 '18 at 12:00
I can't join on Value field for other reasons Alright, then join those tables on what?T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?
– Sami
Nov 21 '18 at 12:03
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04
2
2
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't use
value1 = value2 in the JOIN, then what determines the relationship?).– Larnu
Nov 21 '18 at 12:00
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't use
value1 = value2 in the JOIN, then what determines the relationship?).– Larnu
Nov 21 '18 at 12:00
I can't join on Value field for other reasons Alright, then join those tables on what?
T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?– Sami
Nov 21 '18 at 12:03
I can't join on Value field for other reasons Alright, then join those tables on what?
T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?– Sami
Nov 21 '18 at 12:03
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04
|
show 6 more comments
2 Answers
2
active
oldest
votes
1
How does this look?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value100and then 3 rows with the value99then you would get aJOINbetween the 2nd row of99and the first of100. (100 + '1' = 101 = 99 + '2')
– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
add a comment |
2
Here is one way to get your desired output:
;WITH CTE1 AS
(
SELECT [No],
[Value1],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value1] ORDER BY @@SPID) As rn
FROM #TestTable1
), CTE2 AS
(
SELECT [No],
[Value2],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value2] ORDER BY @@SPID) As rn
FROM #TestTable2
)
SELECT C1.[No],
C1.[Desc],
C1.[Value1],
C2.[Value2]
FROM CTE1 AS C1
LEFT JOIN CTE2 AS C2
ON C1.[No] = C2.[No]
AND C1.rn = C2.rn
AND C1.[Value1] = C2.[Value2]
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
|
show 1 more 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%2f53411502%2fsql-server-duplicate-joins-issue%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
How does this look?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value100and then 3 rows with the value99then you would get aJOINbetween the 2nd row of99and the first of100. (100 + '1' = 101 = 99 + '2')
– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
add a comment |
1
How does this look?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value100and then 3 rows with the value99then you would get aJOINbetween the 2nd row of99and the first of100. (100 + '1' = 101 = 99 + '2')
– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
add a comment |
1
1
1
How does this look?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
How does this look?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
edited Nov 21 '18 at 12:19
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
answered Nov 21 '18 at 12:08
DohsanDohsan
2768
2768
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value100and then 3 rows with the value99then you would get aJOINbetween the 2nd row of99and the first of100. (100 + '1' = 101 = 99 + '2')
– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
add a comment |
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value100and then 3 rows with the value99then you would get aJOINbetween the 2nd row of99and the first of100. (100 + '1' = 101 = 99 + '2')
– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
That's what I am looking for. Thanks a lot
– user3583912
Nov 21 '18 at 12:14
I'd be a little careful here. You could get false positives. For example, if you had a row with the value
100 and then 3 rows with the value 99 then you would get a JOIN between the 2nd row of 99 and the first of 100. (100 + '1' = 101 = 99 + '2')– Larnu
Nov 21 '18 at 12:15
I'd be a little careful here. You could get false positives. For example, if you had a row with the value
100 and then 3 rows with the value 99 then you would get a JOIN between the 2nd row of 99 and the first of 100. (100 + '1' = 101 = 99 + '2')– Larnu
Nov 21 '18 at 12:15
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
Good point, perhaps with some kind of separator symbol inbetween. Although still not sure why value can't be used in the join :)
– Dohsan
Nov 21 '18 at 12:18
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
I'd added the missing cast to the value as well so it shouldn't
– Dohsan
Nov 21 '18 at 12:22
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
Hi @Dohsan, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:34
add a comment |
2
Here is one way to get your desired output:
;WITH CTE1 AS
(
SELECT [No],
[Value1],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value1] ORDER BY @@SPID) As rn
FROM #TestTable1
), CTE2 AS
(
SELECT [No],
[Value2],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value2] ORDER BY @@SPID) As rn
FROM #TestTable2
)
SELECT C1.[No],
C1.[Desc],
C1.[Value1],
C2.[Value2]
FROM CTE1 AS C1
LEFT JOIN CTE2 AS C2
ON C1.[No] = C2.[No]
AND C1.rn = C2.rn
AND C1.[Value1] = C2.[Value2]
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
|
show 1 more comment
2
Here is one way to get your desired output:
;WITH CTE1 AS
(
SELECT [No],
[Value1],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value1] ORDER BY @@SPID) As rn
FROM #TestTable1
), CTE2 AS
(
SELECT [No],
[Value2],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value2] ORDER BY @@SPID) As rn
FROM #TestTable2
)
SELECT C1.[No],
C1.[Desc],
C1.[Value1],
C2.[Value2]
FROM CTE1 AS C1
LEFT JOIN CTE2 AS C2
ON C1.[No] = C2.[No]
AND C1.rn = C2.rn
AND C1.[Value1] = C2.[Value2]
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
|
show 1 more comment
2
2
2
Here is one way to get your desired output:
;WITH CTE1 AS
(
SELECT [No],
[Value1],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value1] ORDER BY @@SPID) As rn
FROM #TestTable1
), CTE2 AS
(
SELECT [No],
[Value2],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value2] ORDER BY @@SPID) As rn
FROM #TestTable2
)
SELECT C1.[No],
C1.[Desc],
C1.[Value1],
C2.[Value2]
FROM CTE1 AS C1
LEFT JOIN CTE2 AS C2
ON C1.[No] = C2.[No]
AND C1.rn = C2.rn
AND C1.[Value1] = C2.[Value2]
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
Here is one way to get your desired output:
;WITH CTE1 AS
(
SELECT [No],
[Value1],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value1] ORDER BY @@SPID) As rn
FROM #TestTable1
), CTE2 AS
(
SELECT [No],
[Value2],
[Desc],
ROW_NUMBER() OVER(PARTITION BY [No], [Value2] ORDER BY @@SPID) As rn
FROM #TestTable2
)
SELECT C1.[No],
C1.[Desc],
C1.[Value1],
C2.[Value2]
FROM CTE1 AS C1
LEFT JOIN CTE2 AS C2
ON C1.[No] = C2.[No]
AND C1.rn = C2.rn
AND C1.[Value1] = C2.[Value2]
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
answered Nov 21 '18 at 12:13
Zohar PeledZohar Peled
55.4k73374
55.4k73374
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
|
show 1 more comment
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Thank you so much, working fine.
– user3583912
Nov 21 '18 at 12:14
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Glad to help :-). BTW, the false positives Larnu wrote about in the comments of the other answers will not happen with this one.
– Zohar Peled
Nov 21 '18 at 12:22
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
Hi @Zohar Peled, I got another situation with the above example, can you help?
– user3583912
Nov 21 '18 at 15:37
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
perhaps. Post another question, me or someone else will probably be able to answer it.
– Zohar Peled
Nov 21 '18 at 16:16
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
Hi @Zohar Peled,Sure, Thanks
– user3583912
Nov 21 '18 at 16:23
|
show 1 more 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%2f53411502%2fsql-server-duplicate-joins-issue%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
2
So, what is the relationship between these 2 tables that share no distinct ID/foreign key?
– Larnu
Nov 21 '18 at 11:58
@Larnu, unfortunately, I don't have any other Id/foreign key to make it unique
– user3583912
Nov 21 '18 at 11:59
That wasn't what I was asking. I'm asking what the relationship between the 2 tables is, as there isn't an obvious one (considering you said that you can't use
value1 = value2in theJOIN, then what determines the relationship?).– Larnu
Nov 21 '18 at 12:00
I can't join on Value field for other reasons Alright, then join those tables on what?
T1.No = T2.No? I tried to use row_number() but still not coming as my desired output Can you please post the query you try it and brings unexpected result?– Sami
Nov 21 '18 at 12:03
I need to compare two data sets and bring first dataset results if matches bring both values if not NULL.
– user3583912
Nov 21 '18 at 12:04