CODEWITHSUNDEEP
sql-server-2008
None
None

Reputation: 5670

Select query with a condition in SQL

I have two tables Table-A and Table-B.

Table-A contains

id           
1
2
3
4

Table-B contains 

id    tno  data
1      1    regec
1      2    marsec
1      0    lorem
2      1    ipsum
2      0    doller
3      2    sit
3      0    amet
3      1    lipsum

In these tables the id column is the primary key. I want to get all id's from Table-A, which don't have a corresponding row in Table-B with tno as '2'.

My result set looks like this

id
2
4

Upvotes: 0

Views: 133

Answers (3)

Itay Gal
Itay Gal

Reputation: 10824

SELECT id FROM tableA WHERE id NOT IN (SELECT id FROM tableB WHERE tno=2)

Upvotes: 3

Suraj Shrestha
Suraj Shrestha

Reputation: 1808

SELECT id FROM [Table-A] where Id not in(SELECT id FROM [Table-B] WHERE tno=2)

Upvotes: 2

mvp
mvp

Reputation: 116427

SELECT a.id
FROM tableA a
WHERE a.id NOT IN (
    SELECT b.id
    FROM tableB b
    WHERE b.tno = 2
)

SQLFIddle Demo

Upvotes: 5

Related Questions