non matching records in Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

non matching records in Joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-01 : 08:05:40

amit writes "can we use joins to retrieve the non matching records in two tables?

for e.g

Table Dept has following fields
1.Depid -primary key
2.Depname
depid depname
1 a
2 b
3 c
4 d
5 e
Table Emp has following fields

1.empid - Primary Key
2.name
3.depid _ foreign key

empid empname deptid
1 q 1
2 w 3
3 r 2

can we retrieve those department id which are not in emp table
using joins i.e i want the depid 4 and 5 which are not in emp table"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-01 : 09:04:27

[code]
select depid
from dept d
left outer join emp e on e.deptid = d.depid
where e.empid is null
[/code]