[dba-SQLServer]RE: Simple SQL question?

select distinct [Employee Number]=e.EmployeeNumber, LastName=e.Surname, Status='Active' from EmployeeTransaction e where exists (select * from EmployeeTransaction e2 where e.EmployeeNumber=e2.EmployeeNumber and Active='TRUE')
union
select distinct e.EmployeeNumber, e.Surname,'Inactive' from EmployeeTransaction e where not exists (select * from EmployeeTransaction e2 where e.EmployeeNumber=e2.EmployeeNumber and Active='TRUE')

______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
? (210) 753-3148 - pager

 -----Original Message-----
From: 	Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] 
Sent:	Thursday, February 06, 2003 6:07 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	[dba-SQLServer]RE: Simple SQL question?

Hi All:

My mind has just gone blank.

I want to check a table for all records for a specific 'EmployeeNumber'.
There might be one record and there might be a many as 10 records, in this
table.
All the records have a boolean flag that marks them as either being Acive or
Inactive.

I would like to return ONE value of TRUE if any records are active or
FALSE...
and this process would be a subquery of another query.

Example:

select distinct e.EmployeeNumber as [Employee Number], e.Surname as
[Lastname],
Status = (select count(e2.active)as [Status Count] from EmployeeTransaction
as e2 where Active = TRUE and e2.EmployeeNumber = e.EmployeeNumber)
from EmployeeTransaction as e

The above example doesn't work of course, because a number is returned but
if the imbedded subquery would return ONE result of TRUE or FALSE, it would.
(In the example above a value of greater than one would be TRUE result and
zero would be FALSE result.) I am sure it is simple but at four in the
morning, here, it is not.

TIA
Jim

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com