October 20, 2008 at 6:37 am
I know this is not the best-written query, but the following worked in SQL 2000 for almost 4 months. Now SQL Server will not return any records (and I know it has to return some records). I can run this same query in another database (with the same name, different machine) using the same tables, and it works just fine. Any ideas why it falis in one database and not in another?
SELECT FirstName,LastName,EmpEmail,EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN
(select EmpCode from tblEmpCodes where cancelled is NOT NULL)
AND Subgroup = @SubGroup
ORDER BY LastName
Thanks for your replies.
October 20, 2008 at 7:16 am
Not really enough information for us to go on at this point.
What rowcounts do these statements return on the database you get nothing from?
SELECT FirstName,LastName,EmpEmail,EmpCode
FROM tblEmployees
SELECT FirstName,LastName,EmpEmail,EmpCode
FROM tblEmployees
WHERE Subgroup = @Subgroup
SELECT EmpCode FROM tblEmpCodes where cancelled IS NOT NULL
Select EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL)
SELECT Empcode
FROM tblEmployees
WHERE EmpCode IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL)
October 20, 2008 at 7:23 am
All of the queries return rows except this one:
Select EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL)
No rows returned.
October 20, 2008 at 7:33 am
Sorry, here is the info. you requested:
SELECT FirstName,LastName,EmpEmail,EmpCode
FROM tblEmployees (7990)
SELECT FirstName,LastName,EmpEmail,EmpCode
FROM tblEmployees
WHERE Subgroup = @Subgroup (VARIES ACCORDING TO @SubGroup)
SELECT EmpCode FROM tblEmpCodes where cancelled IS NOT NULL (1906)
Select EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL) (none)
SELECT Empcode
FROM tblEmployees
WHERE EmpCode IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL) (1156 rows)
October 20, 2008 at 7:33 am
dletz (10/20/2008)
All of the queries return rows except this one:Select EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL)
No rows returned.
This then indicates that your [tblEmpCodes] table has all of the EmpCodes in it and they are not cancelled (assuming that [cacelled] comes from that table).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 7:43 am
Select EmpCode
FROM tblEmployees
WHERE EmpCode NOT IN (select EmpCode from tblEmpCodes where cancelled is NOT NULL) (none)
That one there takes subgroup out of the equation.
As Barry said, it means none of the Employees have empcodes that are cancelled.
The inner query generates a list of empcodes that are cancelled (presumably at least, given the not null), and then you select the employees whose empcodes are not in this list(either not cancelled, or not in the table at all). Is that what you're attempting to show?
October 20, 2008 at 7:50 am
Thanks for the replies.
Yes, i want to display all employees (from tblEmployees) whose employee code is not cancelled in tblEmployeeCodes.
October 20, 2008 at 2:06 pm
Run this:
select EmpCode
from tblEmpCodes
where cancelled is NOT NULL
AND EmpCode IS NULL
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply