Left Join repeat for each employee
0
I am using Oracle 11g and SQL Developer 16.260.
TABLE 1 - EMPLOYEE
ID FIRST_NAME LAST_NAME
1 CHRIS PETERS
2 MICHELLE ANDREWS
3 ANDY JONES
TABLE 2 – SKILLS
ID SKILL
1 WRITING
2 READING
3 COMPUTERS
4 EXCEL
5 TYPING
6 MS WORD
TABLE 3 – EMPLOYEE_SKILLS
ID EMPLOYEE_ID SKILLS_ID
1 1 1
2 1 2
3 1 4
4 2 1
5 2 2
6 2 3
7 2 6
8 3 1
9 3 6
10 3 5
I am looking for a query that will give me the following results
EMPLOYEE.FIRST_NAME EMPLOYEE.LAST_NAME SKILL COMPLETE
CHRIS PETERS WRITING Complete
CHRIS PETERS READING Complete
CHRIS PETERS COMPUTERS Incomplete
CHRIS PETERS EXCEL Complete
CHRIS PETERS TYPING Incomplete
CHRIS PETERS MS WORD Incomplete
MICHELLE ANDREWS WRITING Complete
MICHELLE ANDREWS READING Complete
MICHELLE ANDREWS COMPUTERS Complete
MICHELLE ANDREWS EXCEL Incomplete
MICHELLE ANDREWS TYPING Incomplete
MICHELLE ANDREWS MS WORD Complete
ANDY JONES WRITING Complete
ANDY JONES READING Incomplete
ANDY JONES COMPUTERS Incomplete
ANDY JONES EXCEL Incomplete
ANDY JONES TYPING Complete
ANDY JONES MS WORD Complete
I have tried and other variations of
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL, CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'COMPLETE' END
FROM EMPLOYEE E, EMPLOYEE_SKILLS ES
LEFT JOIN SKILLS S ON S.ID = ES.SKILLS_ID
WHERE EMPLOYEE.ID=EMPLOYEE_SKILLS.EMPLOYEE_ID
I can not seem to have the SKILLS repeat for each employee.
sql oracle oracle11g left-join
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
add a comment |
0
I am using Oracle 11g and SQL Developer 16.260.
TABLE 1 - EMPLOYEE
ID FIRST_NAME LAST_NAME
1 CHRIS PETERS
2 MICHELLE ANDREWS
3 ANDY JONES
TABLE 2 – SKILLS
ID SKILL
1 WRITING
2 READING
3 COMPUTERS
4 EXCEL
5 TYPING
6 MS WORD
TABLE 3 – EMPLOYEE_SKILLS
ID EMPLOYEE_ID SKILLS_ID
1 1 1
2 1 2
3 1 4
4 2 1
5 2 2
6 2 3
7 2 6
8 3 1
9 3 6
10 3 5
I am looking for a query that will give me the following results
EMPLOYEE.FIRST_NAME EMPLOYEE.LAST_NAME SKILL COMPLETE
CHRIS PETERS WRITING Complete
CHRIS PETERS READING Complete
CHRIS PETERS COMPUTERS Incomplete
CHRIS PETERS EXCEL Complete
CHRIS PETERS TYPING Incomplete
CHRIS PETERS MS WORD Incomplete
MICHELLE ANDREWS WRITING Complete
MICHELLE ANDREWS READING Complete
MICHELLE ANDREWS COMPUTERS Complete
MICHELLE ANDREWS EXCEL Incomplete
MICHELLE ANDREWS TYPING Incomplete
MICHELLE ANDREWS MS WORD Complete
ANDY JONES WRITING Complete
ANDY JONES READING Incomplete
ANDY JONES COMPUTERS Incomplete
ANDY JONES EXCEL Incomplete
ANDY JONES TYPING Complete
ANDY JONES MS WORD Complete
I have tried and other variations of
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL, CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'COMPLETE' END
FROM EMPLOYEE E, EMPLOYEE_SKILLS ES
LEFT JOIN SKILLS S ON S.ID = ES.SKILLS_ID
WHERE EMPLOYEE.ID=EMPLOYEE_SKILLS.EMPLOYEE_ID
I can not seem to have the SKILLS repeat for each employee.
sql oracle oracle11g left-join
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
Switch to explicitJOINeverywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.
– jarlh
Nov 27 '18 at 20:45
add a comment |
0
0
0
I am using Oracle 11g and SQL Developer 16.260.
TABLE 1 - EMPLOYEE
ID FIRST_NAME LAST_NAME
1 CHRIS PETERS
2 MICHELLE ANDREWS
3 ANDY JONES
TABLE 2 – SKILLS
ID SKILL
1 WRITING
2 READING
3 COMPUTERS
4 EXCEL
5 TYPING
6 MS WORD
TABLE 3 – EMPLOYEE_SKILLS
ID EMPLOYEE_ID SKILLS_ID
1 1 1
2 1 2
3 1 4
4 2 1
5 2 2
6 2 3
7 2 6
8 3 1
9 3 6
10 3 5
I am looking for a query that will give me the following results
EMPLOYEE.FIRST_NAME EMPLOYEE.LAST_NAME SKILL COMPLETE
CHRIS PETERS WRITING Complete
CHRIS PETERS READING Complete
CHRIS PETERS COMPUTERS Incomplete
CHRIS PETERS EXCEL Complete
CHRIS PETERS TYPING Incomplete
CHRIS PETERS MS WORD Incomplete
MICHELLE ANDREWS WRITING Complete
MICHELLE ANDREWS READING Complete
MICHELLE ANDREWS COMPUTERS Complete
MICHELLE ANDREWS EXCEL Incomplete
MICHELLE ANDREWS TYPING Incomplete
MICHELLE ANDREWS MS WORD Complete
ANDY JONES WRITING Complete
ANDY JONES READING Incomplete
ANDY JONES COMPUTERS Incomplete
ANDY JONES EXCEL Incomplete
ANDY JONES TYPING Complete
ANDY JONES MS WORD Complete
I have tried and other variations of
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL, CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'COMPLETE' END
FROM EMPLOYEE E, EMPLOYEE_SKILLS ES
LEFT JOIN SKILLS S ON S.ID = ES.SKILLS_ID
WHERE EMPLOYEE.ID=EMPLOYEE_SKILLS.EMPLOYEE_ID
I can not seem to have the SKILLS repeat for each employee.
sql oracle oracle11g left-join
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
I am using Oracle 11g and SQL Developer 16.260.
TABLE 1 - EMPLOYEE
ID FIRST_NAME LAST_NAME
1 CHRIS PETERS
2 MICHELLE ANDREWS
3 ANDY JONES
TABLE 2 – SKILLS
ID SKILL
1 WRITING
2 READING
3 COMPUTERS
4 EXCEL
5 TYPING
6 MS WORD
TABLE 3 – EMPLOYEE_SKILLS
ID EMPLOYEE_ID SKILLS_ID
1 1 1
2 1 2
3 1 4
4 2 1
5 2 2
6 2 3
7 2 6
8 3 1
9 3 6
10 3 5
I am looking for a query that will give me the following results
EMPLOYEE.FIRST_NAME EMPLOYEE.LAST_NAME SKILL COMPLETE
CHRIS PETERS WRITING Complete
CHRIS PETERS READING Complete
CHRIS PETERS COMPUTERS Incomplete
CHRIS PETERS EXCEL Complete
CHRIS PETERS TYPING Incomplete
CHRIS PETERS MS WORD Incomplete
MICHELLE ANDREWS WRITING Complete
MICHELLE ANDREWS READING Complete
MICHELLE ANDREWS COMPUTERS Complete
MICHELLE ANDREWS EXCEL Incomplete
MICHELLE ANDREWS TYPING Incomplete
MICHELLE ANDREWS MS WORD Complete
ANDY JONES WRITING Complete
ANDY JONES READING Incomplete
ANDY JONES COMPUTERS Incomplete
ANDY JONES EXCEL Incomplete
ANDY JONES TYPING Complete
ANDY JONES MS WORD Complete
I have tried and other variations of
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL, CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'COMPLETE' END
FROM EMPLOYEE E, EMPLOYEE_SKILLS ES
LEFT JOIN SKILLS S ON S.ID = ES.SKILLS_ID
WHERE EMPLOYEE.ID=EMPLOYEE_SKILLS.EMPLOYEE_ID
I can not seem to have the SKILLS repeat for each employee.
sql oracle oracle11g left-join
sql oracle oracle11g left-join
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
edited Nov 28 '18 at 8:42
Barbaros Özhan
14k71634
14k71634
asked Nov 27 '18 at 20:44
user3297770user3297770
74
asked Nov 27 '18 at 20:44
user3297770user3297770
74
asked Nov 27 '18 at 20:44
user3297770user3297770
74
74
Switch to explicitJOINeverywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.
– jarlh
Nov 27 '18 at 20:45
add a comment |
Switch to explicitJOINeverywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.
– jarlh
Nov 27 '18 at 20:45
Switch to explicit
JOIN everywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.– jarlh
Nov 27 '18 at 20:45
Switch to explicit
JOIN everywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.– jarlh
Nov 27 '18 at 20:45
add a comment |
1 Answer
1
active
oldest
votes
1
Use a cross join to generate the rows and a left join to bring in the values:
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL,
(CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'Complete' END)
FROM EMPLOYEE E CROSS JOIN
SKILLS S LEFT JOIN
EMPLOYEE_SKILLS ES
ON S.ID = ES.SKILLS_ID AND E.ID = ES.EMPLOYEE_ID;
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
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%2f53507851%2fleft-join-repeat-for-each-employee%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
1
Use a cross join to generate the rows and a left join to bring in the values:
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL,
(CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'Complete' END)
FROM EMPLOYEE E CROSS JOIN
SKILLS S LEFT JOIN
EMPLOYEE_SKILLS ES
ON S.ID = ES.SKILLS_ID AND E.ID = ES.EMPLOYEE_ID;
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
add a comment |
1
Use a cross join to generate the rows and a left join to bring in the values:
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL,
(CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'Complete' END)
FROM EMPLOYEE E CROSS JOIN
SKILLS S LEFT JOIN
EMPLOYEE_SKILLS ES
ON S.ID = ES.SKILLS_ID AND E.ID = ES.EMPLOYEE_ID;
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
add a comment |
1
1
1
Use a cross join to generate the rows and a left join to bring in the values:
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL,
(CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'Complete' END)
FROM EMPLOYEE E CROSS JOIN
SKILLS S LEFT JOIN
EMPLOYEE_SKILLS ES
ON S.ID = ES.SKILLS_ID AND E.ID = ES.EMPLOYEE_ID;
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
Use a cross join to generate the rows and a left join to bring in the values:
SELECT E.FIRST_NAME, E.LAST_NAME, S.SKILL,
(CASE ES.SKILLS_ID IS NULL THEN 'Incomplete' ELSE 'Complete' END)
FROM EMPLOYEE E CROSS JOIN
SKILLS S LEFT JOIN
EMPLOYEE_SKILLS ES
ON S.ID = ES.SKILLS_ID AND E.ID = ES.EMPLOYEE_ID;
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 20:46
Gordon LinoffGordon Linoff
784k35310415
784k35310415
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
add a comment |
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
I could hug you! Thank you so very much, you are awesome!
– user3297770
Nov 27 '18 at 20:58
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
Again! Thank you!
– user3297770
Nov 27 '18 at 21:03
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%2f53507851%2fleft-join-repeat-for-each-employee%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
Switch to explicit
JOINeverywhere! Mixing OUTER JOIN with old, comma separated, implicit join is a mess.– jarlh
Nov 27 '18 at 20:45