Jim Lawrence (AccessD)
accessd at shaw.ca
Thu Feb 6 09:38:48 CST 2003
Hi Suzan: There may be a number of records, all may be inactive, some may be active, all may be active but if a minimum of one record is active it needs to return a TRUE otherwise FALSE... I am obviously missing something. Jim -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Susan Harkins Sent: Thursday, February 06, 2003 6:59 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer]RE: Simple SQL question? Well, off the top of my head, can't you just return the records Boolean field instead of the employee number and last name field? Susan H. > 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 > > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com