"Guy Cohen" <noemail@please.com> wrote in message
news:ufltOKu$HHA.3900@TK2MSFTNGP02.phx.gbl...
> Hi all
> I hope this is the right place to ask.........:)
>
> I have this case -
>
> I have a list of employees that should get a letter this month (e.g. happy
> birthday), also their 'rank A' manager should get a letter.
>
> These are the tables:
>
> Table1 =tblBirthdayList = The table with the list of the employees that
> should get a letter this month, lets say it has a column EmployeeID
> Table2 = tblEmployees = a list of the employees and their rank (columns
> will be: EmployeeID amd Rank)
> Table3 = tblEmployeesManagers = a table that lists employees and their
> managers.
>
> If during the search for the employees and their managers a 'rank B'
> manager is found (above that employee) , he should not be notified - but
> HIS manager should.
>
> Example:
>
> tblBirthday
> EmployeeID
> 1
> 2
> 3
> 4
>
> tblEmployees
> EmployeeID, Rank
> 1,C
> 2,C
> 3,C
> 4,A
> 5,A
> 6,B
> 7,A
>
> tblEmployeesManagers
> EmployeeID,ManagerID
> 1,7
> 2,5
> 3,6
> 4,7
> 6,7
>
> In this sample - employees 1,2,3,4 should get a letter
> Their managers are:
> 1==>7
> 2==>5
> 3==>6
> 4==>7
>
> But 6 has rank B
> His manager is :
> 6==>7
>
> Conclusion:
> Manager 5 should get a letter(birthday reminder) about employee 2
> Manager 7 should get a letter about employees 1,3,7
>
> How do I do that with a select phrase
> If not possible - how do I do that with a stored procedure?
>
> I hope it is clear. :)
> TIA
> Guy
>
>
>