CODEWITHSUNDEEP
sqlsql-servert-sql
J.Doe
J.Doe

Reputation: 139

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?

Upvotes: 0

Views: 59

Answers (4)

George Menoutis
George Menoutis

Reputation: 7250

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)

Upvotes: 0

Ravi Kanex
Ravi Kanex

Reputation: 1

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.

Upvotes: 0

Gauravsa
Gauravsa

Reputation: 6528

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;

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

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
        )

Upvotes: 2

Related Questions