How do i get the list of people not in an inner join?
I am trying to get the exact list
of people who are not part of an inner list. I am not sure if its another join, a NOT EXISTS or a NOT IN function that i should use:
SELECT *
FROM db.user
WHERE NOT EXISTS (SELECT DISTINCT email, m.id, u.status
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
Unfortunately the output is blank when I run this query. Althought about 1 million rows should appear.
sql
sql-server asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
add a comment |
I am trying to get the exact list
of people who are not part of an inner list. I am not sure if its another join, a NOT EXISTS or a NOT IN function that i should use:
SELECT *
FROM db.user
WHERE NOT EXISTS (SELECT DISTINCT email, m.id, u.status
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
Unfortunately the output is blank when I run this query. Althought about 1 million rows should appear.
sql
sql-server asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36
add a comment |
I am trying to get the exact list
of people who are not part of an inner list. I am not sure if its another join, a NOT EXISTS or a NOT IN function that i should use:
SELECT *
FROM db.user
WHERE NOT EXISTS (SELECT DISTINCT email, m.id, u.status
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
Unfortunately the output is blank when I run this query. Althought about 1 million rows should appear.
sql
sql-server asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
I am trying to get the exact list
of people who are not part of an inner list. I am not sure if its another join, a NOT EXISTS or a NOT IN function that i should use:
SELECT *
FROM db.user
WHERE NOT EXISTS (SELECT DISTINCT email, m.id, u.status
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
Unfortunately the output is blank when I run this query. Althought about 1 million rows should appear.
sql
sql-server sql
sql-server asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
asked Nov 22 '18 at 21:32
Roger SteinbergRoger Steinberg
338113
338113
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36
add a comment |
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36
1
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36
add a comment |
2 Answers
2
active
oldest
votes
You can try the query below, which is including a left outer join instead
select distinct u.*
from db.user u
left outer join db.member m on m.id = u.member_id
where m.id is null
Or if u insist using inner join, then try:
;with cte as (
SELECT DISTINCT m.id,
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
select *
from db.users u
where u.member_id not in (select id from cte)
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You have used U as an alias but it is not defined. Also, you don't need to JOIN when you using EXISTS(). This is correct usage for EXISTS():
SELECT *
FROM db.user U
WHERE NOT EXISTS (SELECT 1
FROM db.member m
WHERE m.id=u.member_id
)
edited Nov 22 '18 at 22:05
Sami
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
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%2f53438190%2fhow-do-i-get-the-list-of-people-not-in-an-inner-join%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
You can try the query below, which is including a left outer join instead
select distinct u.*
from db.user u
left outer join db.member m on m.id = u.member_id
where m.id is null
Or if u insist using inner join, then try:
;with cte as (
SELECT DISTINCT m.id,
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
select *
from db.users u
where u.member_id not in (select id from cte)
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You can try the query below, which is including a left outer join instead
select distinct u.*
from db.user u
left outer join db.member m on m.id = u.member_id
where m.id is null
Or if u insist using inner join, then try:
;with cte as (
SELECT DISTINCT m.id,
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
select *
from db.users u
where u.member_id not in (select id from cte)
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You can try the query below, which is including a left outer join instead
select distinct u.*
from db.user u
left outer join db.member m on m.id = u.member_id
where m.id is null
Or if u insist using inner join, then try:
;with cte as (
SELECT DISTINCT m.id,
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
select *
from db.users u
where u.member_id not in (select id from cte)
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
You can try the query below, which is including a left outer join instead
select distinct u.*
from db.user u
left outer join db.member m on m.id = u.member_id
where m.id is null
Or if u insist using inner join, then try:
;with cte as (
SELECT DISTINCT m.id,
FROM db.member m
INNER JOIN db.user u
ON m.id=u.member_id
)
select *
from db.users u
where u.member_id not in (select id from cte)
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
answered Nov 22 '18 at 21:39
Eray BalkanliEray Balkanli
4,29852144
4,29852144
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
same problem , no rows are shown
– Roger Steinberg
Nov 22 '18 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You have used U as an alias but it is not defined. Also, you don't need to JOIN when you using EXISTS(). This is correct usage for EXISTS():
SELECT *
FROM db.user U
WHERE NOT EXISTS (SELECT 1
FROM db.member m
WHERE m.id=u.member_id
)
edited Nov 22 '18 at 22:05
Sami
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You have used U as an alias but it is not defined. Also, you don't need to JOIN when you using EXISTS(). This is correct usage for EXISTS():
SELECT *
FROM db.user U
WHERE NOT EXISTS (SELECT 1
FROM db.member m
WHERE m.id=u.member_id
)
edited Nov 22 '18 at 22:05
Sami
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
You have used U as an alias but it is not defined. Also, you don't need to JOIN when you using EXISTS(). This is correct usage for EXISTS():
SELECT *
FROM db.user U
WHERE NOT EXISTS (SELECT 1
FROM db.member m
WHERE m.id=u.member_id
)
edited Nov 22 '18 at 22:05
Sami
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
You have used U as an alias but it is not defined. Also, you don't need to JOIN when you using EXISTS(). This is correct usage for EXISTS():
SELECT *
FROM db.user U
WHERE NOT EXISTS (SELECT 1
FROM db.member m
WHERE m.id=u.member_id
)
edited Nov 22 '18 at 22:05
Sami
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
edited Nov 22 '18 at 22:05
Sami
8,85831241
edited Nov 22 '18 at 22:05
Sami
8,85831241
edited Nov 22 '18 at 22:05
Sami
8,85831241
8,85831241
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
answered Nov 22 '18 at 21:45
Zeki GumusZeki Gumus
1,382212
1,382212
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
add a comment |
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
works perfectly thank you!
– Roger Steinberg
Nov 22 '18 at 21:59
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%2f53438190%2fhow-do-i-get-the-list-of-people-not-in-an-inner-join%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
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 '18 at 21:36