CODEWITHSUNDEEP
sqlsql-server
sandeep kumar
sandeep kumar

Reputation: 11

How to filter records using two tables

I have two tables like below:

Table 1 

id  name    enrollno    subjectname batchname   groupname
1   abc       a1          s1          b1            g1
2   xyz       a2          s1          b2            g1

Table 2

sid subjectname batchname   groupname
1   s1          b1          g1
2   s2          b2          g1

I want to get records from Table 1 which do not match any records in TABLE 2 like below

name    groupname   batchname   subjectname
xyz     g1          b2           s1

Thanks

Upvotes: 1

Views: 55

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Use NOT EXISTS

SELECT *
FROM   table1 t1
WHERE  NOT EXISTS(SELECT 1
                  FROM   table2 t2
                  WHERE  t2.subjectname = t1.subjectname
                         AND t2.batchname = t1.batchname
                         AND t2.groupname = t1.groupname);

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

You could use a LEFT JOIN here:

SELECT t1.name,
       t1.groupname,
       t1.batchname,
       t1.subjectname
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.subjectname = t2.subjectname AND
       t1.batchname   = t2.batchname AND
       t1.groupname   = t2.groupname
WHERE t2.subjectname IS NULL

This might have a speed advantage if the join columns in table2 had indices setup.

Upvotes: 0

Related Questions