[dba-SQLServer]RE: Simple SQL question?

I'm confused Jim, but I guess this is an Access SQL question?  If it were in
SQL Server I would offer the use of the LEFT OUTER JOIN such as....

use pubs
select A.au_lname + A.au_fname AS Name, Ta.royaltyper,
 'Has books Written ' = CASE
  When Ta.royaltyper IS NULL THEN -1
  ELSE 0
 END
from Authors As A
LEFT OUTER JOIN TitleAuthor As Ta
 ON A.Au_id=Ta.Au_id

good luck...
err, for your case maybe try this...

SELECT DISTINCT  e.EmployeeNumber AS [Employee Number], e.Surname AS
[Lastname],
 'STATUS' = CASE
  WHEN e3.StatusCount = 0 THEN 0
  ELSE -1
 END
FROM  EmployeeTransaction AS e
 LEFT OUTER JOIN (SELECT COUNT(e2.active) AS [Status Count], EmployeeNumber
      FROM EmployeeTransaction AS e2
      WHERE Active = TRUE and e2.EmployeeNumber = e.EmployeeNumber) AS e3
    ON e.EmployeeNumber = e3.EmployeeNumber


-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Thursday, February 06, 2003 8:25 AM
Subject: RE: Re: [dba-SQLServer]RE: Simple SQL question?


: ...Hmmmm...very daring solution...but I don't believe it will fly :)
:
: ______________________________________________________
: 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: fernando.pires at gustavsberg.com
[mailto:fernando.pires at gustavsberg.com]
: Sent: Thursday, February 06, 2003 9:58 AM
: To: dba-sqlserver at databaseadvisors.com
: Subject: Ang: Re: [dba-SQLServer]RE: Simple SQL question?
:
:
: select iif(sum(active) > 0, true,false) from table where active = true
:
: Fernando Pires IT dep.
:
: Tel. + 46 857039367
: Mob. + 46 702795858
:
:
:
:                       "Susan Harkins"
:                       <harkins at iglou.com>                  Till:
<dba-sqlserver at databaseadvisors.com>
:                       Sänt av:                             Kopia:
:                       dba-sqlserver-admin at databasea        Ärende:   Re:
[dba-SQLServer]RE: Simple SQL question?
:                       dvisors.com
:
:
:                       2003-02-06 16:53
:                       Sänd svar till dba-sqlserver
:
:
:
:
:
:
: Which returns true -- the subquery -- you want the subquery to return just
: one true? I don't think you're going to get what you need with a simple
: SELECT or subquery.
:
: Susan H.
:
:
: > 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
: > >