How would I select certain values in a column in one table that is not available in a another table
I have a table that looks like this:
**A B C D E**
1 2 3 4 5
2 3 3 3 3
3 4 5 6 7
4 5 5 5 5
I have another table that looks like this:
**F G H I J**
1 4 7 8 6
2 3 4 5 6
5 1 5 7 8
7 1 5 5 5
I will call the first table Table1 and the second table Table2.
What I'm doing currently is:
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
So, basically I'm joining Table1's A and Table2's F. I'm finding the values that do not are in A but not in F. And then I want to return the B value that corresponds with those A values.
I'm getting an error:
Incorrect syntax near the keyword 'IN'.
How would I fix this?
sql
sql-server tsql edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
add a comment |
I have a table that looks like this:
**A B C D E**
1 2 3 4 5
2 3 3 3 3
3 4 5 6 7
4 5 5 5 5
I have another table that looks like this:
**F G H I J**
1 4 7 8 6
2 3 4 5 6
5 1 5 7 8
7 1 5 5 5
I will call the first table Table1 and the second table Table2.
What I'm doing currently is:
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
So, basically I'm joining Table1's A and Table2's F. I'm finding the values that do not are in A but not in F. And then I want to return the B value that corresponds with those A values.
I'm getting an error:
Incorrect syntax near the keyword 'IN'.
How would I fix this?
sql
sql-server tsql edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
3
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48
add a comment |
I have a table that looks like this:
**A B C D E**
1 2 3 4 5
2 3 3 3 3
3 4 5 6 7
4 5 5 5 5
I have another table that looks like this:
**F G H I J**
1 4 7 8 6
2 3 4 5 6
5 1 5 7 8
7 1 5 5 5
I will call the first table Table1 and the second table Table2.
What I'm doing currently is:
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
So, basically I'm joining Table1's A and Table2's F. I'm finding the values that do not are in A but not in F. And then I want to return the B value that corresponds with those A values.
I'm getting an error:
Incorrect syntax near the keyword 'IN'.
How would I fix this?
sql
sql-server tsql edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
I have a table that looks like this:
**A B C D E**
1 2 3 4 5
2 3 3 3 3
3 4 5 6 7
4 5 5 5 5
I have another table that looks like this:
**F G H I J**
1 4 7 8 6
2 3 4 5 6
5 1 5 7 8
7 1 5 5 5
I will call the first table Table1 and the second table Table2.
What I'm doing currently is:
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
So, basically I'm joining Table1's A and Table2's F. I'm finding the values that do not are in A but not in F. And then I want to return the B value that corresponds with those A values.
I'm getting an error:
Incorrect syntax near the keyword 'IN'.
How would I fix this?
sql
sql-server tsql sql
sql-server tsql edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
edited Nov 21 '18 at 3:49
Rahul Neekhra
6021627
6021627
asked Nov 21 '18 at 3:46
J.Doe
6110
asked Nov 21 '18 at 3:46
J.Doe
6110
asked Nov 21 '18 at 3:46
J.Doe
6110
6110
3
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48
add a comment |
3
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48
3
3
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48
add a comment |
4 Answers
4
active
oldest
votes
Try it like this...
SELECT
t1.B
FROM
dbo.Table1 t1
WHERE
NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t1.A = t2.F
)
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
add a comment |
You can simply do this:
SELECT ts.B
FROM table1 ts
LEFT JOIN table2 tt ON ts.A = tt.F
WHERE ts.A IS NULL;
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
add a comment |
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
In the above query
NOT IN
Clause has been used but not include the resulting column from the table.
ColumnName NOT IN (Select tt.F from tt)
You can use a Left/Right outer join clause and check for NULL value in the appropriate column on where clause.
answered Nov 21 '18 at 6:18
Ravi Kanex
11
add a comment |
For the record, your query is wrong here:
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
The fact that you already mentioned ts.A does not mean the parser "remembers" it. When it comes to the and NOT IN, it doesn't know which thing should be NOT IN. Id est, the correct way would be:
WHERE ts.A = tt.F
and ts.A NOT IN (Select tt.F from tt)
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
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%2f53404983%2fhow-would-i-select-certain-values-in-a-column-in-one-table-that-is-not-available%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try it like this...
SELECT
t1.B
FROM
dbo.Table1 t1
WHERE
NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t1.A = t2.F
)
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
add a comment |
Try it like this...
SELECT
t1.B
FROM
dbo.Table1 t1
WHERE
NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t1.A = t2.F
)
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
add a comment |
Try it like this...
SELECT
t1.B
FROM
dbo.Table1 t1
WHERE
NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t1.A = t2.F
)
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
Try it like this...
SELECT
t1.B
FROM
dbo.Table1 t1
WHERE
NOT EXISTS (
SELECT 1
FROM dbo.Table2 t2
WHERE t1.A = t2.F
)
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
answered Nov 21 '18 at 3:53
Jason A. Long
3,7701412
3,7701412
add a comment |
add a comment |
You can simply do this:
SELECT ts.B
FROM table1 ts
LEFT JOIN table2 tt ON ts.A = tt.F
WHERE ts.A IS NULL;
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
add a comment |
You can simply do this:
SELECT ts.B
FROM table1 ts
LEFT JOIN table2 tt ON ts.A = tt.F
WHERE ts.A IS NULL;
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
add a comment |
You can simply do this:
SELECT ts.B
FROM table1 ts
LEFT JOIN table2 tt ON ts.A = tt.F
WHERE ts.A IS NULL;
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
You can simply do this:
SELECT ts.B
FROM table1 ts
LEFT JOIN table2 tt ON ts.A = tt.F
WHERE ts.A IS NULL;
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
answered Nov 21 '18 at 4:07
Gauravsa
2,2701816
2,2701816
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
add a comment |
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
This one Perfect.
– JERRY
Nov 21 '18 at 6:03
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
an upvote if possible?
– Gauravsa
Nov 21 '18 at 6:07
add a comment |
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
In the above query
NOT IN
Clause has been used but not include the resulting column from the table.
ColumnName NOT IN (Select tt.F from tt)
You can use a Left/Right outer join clause and check for NULL value in the appropriate column on where clause.
answered Nov 21 '18 at 6:18
Ravi Kanex
11
add a comment |
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
In the above query
NOT IN
Clause has been used but not include the resulting column from the table.
ColumnName NOT IN (Select tt.F from tt)
You can use a Left/Right outer join clause and check for NULL value in the appropriate column on where clause.
answered Nov 21 '18 at 6:18
Ravi Kanex
11
add a comment |
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
In the above query
NOT IN
Clause has been used but not include the resulting column from the table.
ColumnName NOT IN (Select tt.F from tt)
You can use a Left/Right outer join clause and check for NULL value in the appropriate column on where clause.
answered Nov 21 '18 at 6:18
Ravi Kanex
11
Select
ts.B
from Table1 ts, Table2 tt
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
In the above query
NOT IN
Clause has been used but not include the resulting column from the table.
ColumnName NOT IN (Select tt.F from tt)
You can use a Left/Right outer join clause and check for NULL value in the appropriate column on where clause.
answered Nov 21 '18 at 6:18
Ravi Kanex
11
answered Nov 21 '18 at 6:18
Ravi Kanex
11
answered Nov 21 '18 at 6:18
Ravi Kanex
11
answered Nov 21 '18 at 6:18
Ravi Kanex
11
11
add a comment |
add a comment |
For the record, your query is wrong here:
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
The fact that you already mentioned ts.A does not mean the parser "remembers" it. When it comes to the and NOT IN, it doesn't know which thing should be NOT IN. Id est, the correct way would be:
WHERE ts.A = tt.F
and ts.A NOT IN (Select tt.F from tt)
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
add a comment |
For the record, your query is wrong here:
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
The fact that you already mentioned ts.A does not mean the parser "remembers" it. When it comes to the and NOT IN, it doesn't know which thing should be NOT IN. Id est, the correct way would be:
WHERE ts.A = tt.F
and ts.A NOT IN (Select tt.F from tt)
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
add a comment |
For the record, your query is wrong here:
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
The fact that you already mentioned ts.A does not mean the parser "remembers" it. When it comes to the and NOT IN, it doesn't know which thing should be NOT IN. Id est, the correct way would be:
WHERE ts.A = tt.F
and ts.A NOT IN (Select tt.F from tt)
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
For the record, your query is wrong here:
WHERE ts.A = tt.F
and NOT IN (Select tt.F from tt)
The fact that you already mentioned ts.A does not mean the parser "remembers" it. When it comes to the and NOT IN, it doesn't know which thing should be NOT IN. Id est, the correct way would be:
WHERE ts.A = tt.F
and ts.A NOT IN (Select tt.F from tt)
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
answered Nov 21 '18 at 7:37
George Menoutis
2,559319
2,559319
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.
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%2f53404983%2fhow-would-i-select-certain-values-in-a-column-in-one-table-that-is-not-available%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
3
Your description on the requirement is a bit confusing. Maybe you can show us your expected result ?
– Squirrel
Nov 21 '18 at 3:48