Multiple rows from Left Join in SQL were rows are uniquely matched
I have two views that I am trying to join. I am joining on three elements, date, case number and surgeon id number. Each should only have one match for the previous case out value, but I am getting multiple rows after my left join.
Here is my code:
CREATE VIEW [dbo].[OR]
AS
SELECT DISTINCT
[ID].*,
[BYSURG].[PREV_PAT_OUT] AS PrevPtOut
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Any insights are much appreciated.
Thanks!
M
sql left-join
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
add a comment |
I have two views that I am trying to join. I am joining on three elements, date, case number and surgeon id number. Each should only have one match for the previous case out value, but I am getting multiple rows after my left join.
Here is my code:
CREATE VIEW [dbo].[OR]
AS
SELECT DISTINCT
[ID].*,
[BYSURG].[PREV_PAT_OUT] AS PrevPtOut
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Any insights are much appreciated.
Thanks!
M
sql left-join
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
5
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38
add a comment |
I have two views that I am trying to join. I am joining on three elements, date, case number and surgeon id number. Each should only have one match for the previous case out value, but I am getting multiple rows after my left join.
Here is my code:
CREATE VIEW [dbo].[OR]
AS
SELECT DISTINCT
[ID].*,
[BYSURG].[PREV_PAT_OUT] AS PrevPtOut
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Any insights are much appreciated.
Thanks!
M
sql left-join
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
I have two views that I am trying to join. I am joining on three elements, date, case number and surgeon id number. Each should only have one match for the previous case out value, but I am getting multiple rows after my left join.
Here is my code:
CREATE VIEW [dbo].[OR]
AS
SELECT DISTINCT
[ID].*,
[BYSURG].[PREV_PAT_OUT] AS PrevPtOut
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Any insights are much appreciated.
Thanks!
M
sql left-join
sql left-join
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
edited Nov 13 '18 at 20:11
marc_s
576k12811111258
576k12811111258
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
asked Nov 13 '18 at 19:58
M_OdonnellM_Odonnell
1
1
5
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38
add a comment |
5
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38
5
5
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38
add a comment |
1 Answer
1
active
oldest
votes
Replace your select block with one that retrieves all columns:
SELECT
*
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Run it and look at your "duplicate" rows - something about them will no longer be a duplicate - perhaps you've forgotten to include some other criteria in your where clause
Putting DISTINCT in the select block is not the answer - find out what data element about the "duplicate" rows is different and then filter out the rows you don't want
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
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%2f53288597%2fmultiple-rows-from-left-join-in-sql-were-rows-are-uniquely-matched%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
Replace your select block with one that retrieves all columns:
SELECT
*
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Run it and look at your "duplicate" rows - something about them will no longer be a duplicate - perhaps you've forgotten to include some other criteria in your where clause
Putting DISTINCT in the select block is not the answer - find out what data element about the "duplicate" rows is different and then filter out the rows you don't want
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
add a comment |
Replace your select block with one that retrieves all columns:
SELECT
*
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Run it and look at your "duplicate" rows - something about them will no longer be a duplicate - perhaps you've forgotten to include some other criteria in your where clause
Putting DISTINCT in the select block is not the answer - find out what data element about the "duplicate" rows is different and then filter out the rows you don't want
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
add a comment |
Replace your select block with one that retrieves all columns:
SELECT
*
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Run it and look at your "duplicate" rows - something about them will no longer be a duplicate - perhaps you've forgotten to include some other criteria in your where clause
Putting DISTINCT in the select block is not the answer - find out what data element about the "duplicate" rows is different and then filter out the rows you don't want
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
Replace your select block with one that retrieves all columns:
SELECT
*
FROM
[dbo].[OR_LOG_INDEXED] [ID]
LEFT JOIN
[DBO].[OR_CASE_NUM] BYSURG ON [ID].[SURG_DT] = [BYSURG].[SURG_DT]
AND [ID].[SURGEON_ID] = [BYSURG].[SURGEON_ID]
AND [ID].[CASE_NUM_BY_ROOM] = [BYSURG].[CASE_NUM_BY_ROOM_ADJ]
Run it and look at your "duplicate" rows - something about them will no longer be a duplicate - perhaps you've forgotten to include some other criteria in your where clause
Putting DISTINCT in the select block is not the answer - find out what data element about the "duplicate" rows is different and then filter out the rows you don't want
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
answered Nov 13 '18 at 20:30
Caius JardCaius Jard
11.7k21239
11.7k21239
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%2f53288597%2fmultiple-rows-from-left-join-in-sql-were-rows-are-uniquely-matched%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
5
You’ll need to include sample data to get a good answer, but most likely is that those three values aren’t unique in at least some cases for those two tables.
– TZHX
Nov 13 '18 at 20:01
Hil You are not clearly describing what output you want in terms of input & you have not given relevant constraints or even example in put & output & desired output. Please act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 13 '18 at 23:38