CODEWITHSUNDEEP
sqlsql-server
tstudent
tstudent

Reputation: 89

find the number of the rows from the first table whose column values are not in the second table in mssql

i have two tables called "system" and "internal" and it has columns (name, ip, entry_no, allocate_no)

i will join this two table by name and ip and i want to get all the rows from table "system" whose "entry_no" is not there in the table "internal"

this is the query i was trying

    select s1.name, s1.ip, s1.entry_no, s1.allocate_no
from system s1 inner join internal as i1 
on s1.name=i1.name and s1.ip = i1.ip
where not exists (
select s2.entry_no
from system s2 inner join internal as i2 
on s2.name=i2.name and s2.ip = i2.ip and s2.entry_no=i2.entry_no
)

system (table)    

name ip entry_no allocate_no 

x     1   222    23

x     1   333    45

y     33  444    66

y     55  555    99

z     55  000    90
 
k    12   1155  99

internal (table)
name ip entry_no allocate_no 

x     1   222    23

x     1   666    45

y     33  444    66

y     33  888    66

y     55  777    99

m     55  000    90

here i want to write a query which should return

x     1   333    45

y     55  555    99

how can i achieve this somebody please help me

Upvotes: 0

Views: 49

Answers (2)

eavom
eavom

Reputation: 1097

My first question is why you are applying inner join between system and internal table? When you apply inner join, you are asking sql server to give you only those row where the data matches between joined table.

If you are not familiar with JOINS, take a look at Joins (SQL Server)

below query should work fine for you

select s.name, s.ip, s.entry_no, s.allocate 
from system s 
where not exists (select 1 from internal i where i.entry_no = s.entry_no and i.ip = s.ip)

Also, you can try EXCEPT, which will give you records from system table that are not available in internal table.

select s.name, s.ip, s.entry_no, s.allocate 
from system s 

except

select i.name, i.ip, i.entry_no, i.allocate 
from internal i

Upvotes: 1

Ravi
Ravi

Reputation: 2281

This will give all the rows of system that are not present in internal:

SELECT s.* 
FROM system AS s
LEFT JOIN internal AS i ON s.ip = i.ip AND s. entry_no = i.entry_no
WHERE i.ip IS NULL

Upvotes: 1

Related Questions