[prog] mysql query

[Meryll Larkin]

Hello Wolf Rising,

How does the Database handle employees with no absences?  Is there a NULL
field somewhere or do they just NOT populate one of the tables?

However you write the query, to get the output you want, your query needs to
match how the information is stored in the database.

Maybe problem is that when an employee has NO absenses there is no record
for that employee in whatever "s_code" you are using to obtain the "count"

You can't find employees with perfect attendence if you included that 
    attendance.status = 'absent' 
in your query UNLESS the ASSOCIATED field has been designed to hold NULLs or
zeros when there are none (then you can search for NULL in that field by
adding an "OR").

Another way to tackle the same problem:  Can you write a query that will
display ONLY employees with perfect attendance in the results?

I hope this helps, if not, maybe you can provide more info about how the
database is designed.

Meryll



 Date: Wed, 29 Mar 2006 23:51:53 -0500
From: "Wolf Rising" <wolfrising at gmail.com>
Subject: [prog] mysql query
To: programming at linuxchix.org
Message-ID:
	<72372a090603292051w7a83f9e1x9d3bfe69e8e65256 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

I am trying to run a query which returns how many employees missed less than
3 shifts during the winter season. The query works except it doesn't show
who had perfect attendance. Is there anyway to modify the current
query to include
those individuals who missed zero shifts? An employee's status for the day
may be recorded as absent, late or present.

Thanks for any assistance :-)

select 
   l_name, 
   f_name, 
   attendance.s_code, 
   season.building_code,
   count(*) 
           as total from 
     sa.attendance, 
     se.employee,
     se.season 
         where 
      attendance.s_code = employee.s_code and
      attendance.s_code = season.s_code and  
      employee.s_code = season.s_code and
      attendance.season = season.season and 
      attendance.year= season.year and
      attendance.status = 'absent' and 
      attendance.season='Winter' and 
      attendance.year=2006 and 
      season.building_code='MAIN'
            group by 
      s_code having count(*) < 4 order  by total asc, l_name;