Left Join without duplicate rows 1 to 1 join. Make each row in one table only join to one row in the other table
-1
I'm trying to join table 1 to table 2 to get table 3. (See desired output) However, I can't seem to get it to work since there are so many options since the table only contains one value. A left join doesn't seem to work.
I found this: Left Join without duplicate rows from left table
which seems to match my use case, but Outer Apply is not in PrestoDB.
I essentially want to match each row in T1 with a single one in T2.
sql
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
add a comment |
-1
I'm trying to join table 1 to table 2 to get table 3. (See desired output) However, I can't seem to get it to work since there are so many options since the table only contains one value. A left join doesn't seem to work.
I found this: Left Join without duplicate rows from left table
which seems to match my use case, but Outer Apply is not in PrestoDB.
I essentially want to match each row in T1 with a single one in T2.
sql
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
2
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06
add a comment |
-1
-1
-1
I'm trying to join table 1 to table 2 to get table 3. (See desired output) However, I can't seem to get it to work since there are so many options since the table only contains one value. A left join doesn't seem to work.
I found this: Left Join without duplicate rows from left table
which seems to match my use case, but Outer Apply is not in PrestoDB.
I essentially want to match each row in T1 with a single one in T2.
sql
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
I'm trying to join table 1 to table 2 to get table 3. (See desired output) However, I can't seem to get it to work since there are so many options since the table only contains one value. A left join doesn't seem to work.
I found this: Left Join without duplicate rows from left table
which seems to match my use case, but Outer Apply is not in PrestoDB.
I essentially want to match each row in T1 with a single one in T2.
sql
sql
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
edited Nov 12 '18 at 20:06
Gordon Linoff
759k35293399
759k35293399
asked Nov 12 '18 at 20:00
apple1234
1
asked Nov 12 '18 at 20:00
apple1234
1
asked Nov 12 '18 at 20:00
apple1234
1
1
2
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06
add a comment |
2
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06
2
2
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06
add a comment |
2 Answers
2
active
oldest
votes
0
If I understand correctly, you can use row_number():
select t1.*, t2.col3
from t1 left join
(select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
from t2
) t2
on t2.col2 = t1.col2 and t2.seqnum = 1;
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
add a comment |
0
If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:
select t1.col1, t1.col2, t2.col3
from
(
select t1.*,
row_number() over (partition by col2 order by col2) as rn
from t1
) as t
left join
(
select t2.*,
row_number() over (partition by col2 order by col2) as rn
from t2
) as t2
on t1.col2 = t2.col2
and t1.rn = t2.rn;
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
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%2f53269269%2fleft-join-without-duplicate-rows-1-to-1-join-make-each-row-in-one-table-only-jo%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
0
If I understand correctly, you can use row_number():
select t1.*, t2.col3
from t1 left join
(select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
from t2
) t2
on t2.col2 = t1.col2 and t2.seqnum = 1;
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
add a comment |
0
If I understand correctly, you can use row_number():
select t1.*, t2.col3
from t1 left join
(select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
from t2
) t2
on t2.col2 = t1.col2 and t2.seqnum = 1;
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
add a comment |
0
0
0
If I understand correctly, you can use row_number():
select t1.*, t2.col3
from t1 left join
(select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
from t2
) t2
on t2.col2 = t1.col2 and t2.seqnum = 1;
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
If I understand correctly, you can use row_number():
select t1.*, t2.col3
from t1 left join
(select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
from t2
) t2
on t2.col2 = t1.col2 and t2.seqnum = 1;
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
answered Nov 12 '18 at 20:09
Gordon Linoff
759k35293399
759k35293399
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
add a comment |
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
This answer would benefit from some words saying how it works..
– Caius Jard
Nov 12 '18 at 20:26
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value.
– apple1234
Nov 12 '18 at 20:28
1
1
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us)
– Caius Jard
Nov 12 '18 at 20:33
1
1
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
@apple1234 . . . This answers the question that you have asked. If you have another question, ask it as a new question, with appropriate details, sample data, and desired results.
– Gordon Linoff
Nov 12 '18 at 20:35
add a comment |
0
If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:
select t1.col1, t1.col2, t2.col3
from
(
select t1.*,
row_number() over (partition by col2 order by col2) as rn
from t1
) as t
left join
(
select t2.*,
row_number() over (partition by col2 order by col2) as rn
from t2
) as t2
on t1.col2 = t2.col2
and t1.rn = t2.rn;
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
add a comment |
0
If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:
select t1.col1, t1.col2, t2.col3
from
(
select t1.*,
row_number() over (partition by col2 order by col2) as rn
from t1
) as t
left join
(
select t2.*,
row_number() over (partition by col2 order by col2) as rn
from t2
) as t2
on t1.col2 = t2.col2
and t1.rn = t2.rn;
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
add a comment |
0
0
0
If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:
select t1.col1, t1.col2, t2.col3
from
(
select t1.*,
row_number() over (partition by col2 order by col2) as rn
from t1
) as t
left join
(
select t2.*,
row_number() over (partition by col2 order by col2) as rn
from t2
) as t2
on t1.col2 = t2.col2
and t1.rn = t2.rn;
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:
select t1.col1, t1.col2, t2.col3
from
(
select t1.*,
row_number() over (partition by col2 order by col2) as rn
from t1
) as t
left join
(
select t2.*,
row_number() over (partition by col2 order by col2) as rn
from t2
) as t2
on t1.col2 = t2.col2
and t1.rn = t2.rn;
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
answered Nov 12 '18 at 22:26
dnoeth
44.8k31838
44.8k31838
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.
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');
);
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%2f53269269%2fleft-join-without-duplicate-rows-1-to-1-join-make-each-row-in-one-table-only-jo%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
I removed the incompatible database tags. Please tag only with the database you are really using.
– Gordon Linoff
Nov 12 '18 at 20:06