help with select statement - 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

SQL Server Development (2000)

help with select statement

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2005-08-08 : 18:30:17

i need to select top 100 records from a table, but only select the top 100 where company.id not in table2.companyid

right now i have the following, but its returning the same company even though they are in table2.

SELECT TOP 100 Id, Name
FROM CompanyBase
WHERE (Id NOT IN
(SELECT Table2.Id FROM Table2
WHERE Table2.Status = 6 OR Table2.Status = 7))
ORDER BY Name

any ideas?

thanks

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-08 : 18:47:58

Your query looks fine to me. You should examine the return values from your IN subquery to make sure it's returning what you expect. Perhaps... it's returning an empty recordset?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-08 : 21:57:11

You should look at the effect that this is having:

WHERE Table2.Status = 6 OR Table2.Status = 7

You will only exclude an ID if the status in Table2 is 6 or 7

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources