Diff between the queries - 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)

Diff between the queries

Author

Topic

srinath
Starting Member

16 Posts

Posted - 2006-10-16 : 07:17:16

Hi All,

I have a query to know the list of employees in a EMP table who are not superiors to any one like

For Ex: In a table EMp with columns EMPNo and MGR I have the data as following
EMPNo MGR
----- ---
7369 7902
7499 7698
7521 7698
7566 7839
7654 7698
7698 7839
7782 7839
7788 7566
7839 NULL
7844 7698
7876 7788
7900 7698
7902 7566
7934 7782

I need to get the records as
EMPNo
-----
7369
7499
7521
7654
7844
7876
7900
7934

I wrote the query as
SELECT distinct EMPNO,MGR FROM DBO.EMP WHERE EMPNO NOT IN (SELECT MGR FROM DBO.EMP) which didn't returny any values

But When i changed the query as
SELECT * FROM DBO.EMP WHERE EMPNO NOT IN (SELECT EMP.EMPNO FROM DBO.EMP EMP,DBO.EMP MGR
WHERE EMP.EMPNO = MGR.MGR )

I am getting the exact answer what required

Can You guys please let me know what is the differnce between the 2 queries and why the first one is not working?

Thank You,
Srinath.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 07:22:49

In the first query, all but one emp has mgr. Writing NOT IN for a NULL value does work.
Change your first query to

SELECT EMPNO, MGR FROM dbo.EMP WHERE EMPNO NOT IN (SELECT MGR FROM dbo.EMP where mgr is not null)

Peter Larsson
Helsingborg, Sweden

srinath
Starting Member

16 Posts

Posted - 2006-10-16 : 07:27:59

Thanks for your reply Peter, I haven't noticed the Null. The query you gave is working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources