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 at 21:32
Roger Steinberg
928
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 at 21:32
Roger Steinberg
928
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 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 at 21:32
Roger Steinberg
928
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 at 21:32
Roger Steinberg
928
asked Nov 22 at 21:32
Roger Steinberg
928
asked Nov 22 at 21:32
Roger Steinberg
928
asked Nov 22 at 21:32
Roger Steinberg
928
asked Nov 22 at 21:32
Roger Steinberg
928
928
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 at 21:36
add a comment |
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 at 21:36
1
1
Some sample data and expected output would be helpful.
– Tony
Nov 22 at 21:36
Some sample data and expected output would be helpful.
– Tony
Nov 22 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 at 21:39
Eray Balkanli
3,88241943
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 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 at 22:05
Sami
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
works perfectly thank you!
– Roger Steinberg
Nov 22 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 at 21:39
Eray Balkanli
3,88241943
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 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 at 21:39
Eray Balkanli
3,88241943
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 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 at 21:39
Eray Balkanli
3,88241943
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 at 21:39
Eray Balkanli
3,88241943
answered Nov 22 at 21:39
Eray Balkanli
3,88241943
answered Nov 22 at 21:39
Eray Balkanli
3,88241943
answered Nov 22 at 21:39
Eray Balkanli
3,88241943
3,88241943
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 at 21:59
add a comment |
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 at 21:59
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
same problem , no rows are shown
– Roger Steinberg
Nov 22 at 21:43
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 at 21:59
both are great and thank you for providing me with alternatives
– Roger Steinberg
Nov 22 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 at 22:05
Sami
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
works perfectly thank you!
– Roger Steinberg
Nov 22 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 at 22:05
Sami
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
works perfectly thank you!
– Roger Steinberg
Nov 22 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 at 22:05
Sami
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
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 at 22:05
Sami
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
edited Nov 22 at 22:05
Sami
7,27431039
edited Nov 22 at 22:05
Sami
7,27431039
edited Nov 22 at 22:05
Sami
7,27431039
7,27431039
answered Nov 22 at 21:45
Zeki Gumus
1,211212
answered Nov 22 at 21:45
Zeki Gumus
1,211212
answered Nov 22 at 21:45
Zeki Gumus
1,211212
1,211212
works perfectly thank you!
– Roger Steinberg
Nov 22 at 21:59
add a comment |
works perfectly thank you!
– Roger Steinberg
Nov 22 at 21:59
works perfectly thank you!
– Roger Steinberg
Nov 22 at 21:59
works perfectly thank you!
– Roger Steinberg
Nov 22 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.
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.
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 at 21:36