Case SQL statement

[winz]

Hi,
First I wish to say that I have tried my best to think, but I still coudn't think "out of box".

I want to perform a SQL statement base on the condition below:

1.) Table A consists of employee data (Emp_Id, Email, Sex, Address, race and nationality)

We have applications which use email address as authentication, so in order to retrieve a correct employee data base on their email, the system won't get the correct row as there are many rows return. So what I did is,


2.) Create a Table B consists of employees whereby their email address is shared with others employees (Those has no email).

But I have no idea how to compute a SQL statement base on these two tables to retrive a correct row for that particular employee who's email is shared by other employee.

I'm trying to solve is, is there any possibility for me to identify the employee base on the email address whose email is shared.

The table B in fact is the solution that I can think of. To store those employees whose email address is shared with other employees. There are about 20 employees whose email address is shared by other. For example:

Employee A email address, shared by Employee B, C, D
Employee F email address, shared by Employee H, I
Employee J email address, shared by Employee K, L, M, N
Employee O email address, shared by Employee P, Q, R, S, T, U

Employee 01 email address - Unique
Employee 02 email address - Unique
Employee 03 email address - Unique
Employee 04 email address - Unique
Employee 05 email address - Unique
Employee 06 email address - -
Employee 07 email address - -
Employee 08 email address - -
Employee 09 email address - -
Employee 10 email address - -
Employee 11 email address - -
Employee 12 email address - -

We have no choice and must use email address as login ID for application A. This is because we use the LDAP feature by intergrate with our Active Directory. In this application A, we required employee data from our Human Resource System to authenticate that she/he is our Active Employee.

Due We have a number of employees who don't have email address, so in our Human Resource System we have to fill in the email column with other employee's email who normally will carry out task on behalf of that group of employees.

The condition I can think of is:

IF the emloyee's emailaddress COUNT(*) > 1), then Validate whether his/her is in Table B, IF TRUE, then this is the "Original" employee. So, select the data.

There will be two main tables involve in the SQL statements, table B and A

Charon
Please advise

[skhanal]

This is not an elagant design. If one person is carrying out tasks for multiple people then when the person logs in how do you know which persons data to retrieve.

Also you have a problem if the person wants to see his own information instead of other persons.

Does the application list a display people the current user manages once logged on?

[winz]

Also you have a problem if the person wants to see his own information instead of other persons.

Those employees have no email address will not login to the applications.

Does the application list a display people the current user manages once logged on?

The system will display the data of the person who login

Charon

[skhanal]

Due We have a number of employees who don't have email address, so in our Human Resource System we have to fill in the email column with other employee's email who normally will carry out task on behalf of that group of employees.

Why don't you create an indicator column on this table to identify people without email.

[Venkata N Rao S]

Your question is a little unclear. This may be a situation where generally some front of office staff will use nearly same feature like you send your requests/applications/questions to emails like admin@xyz.com or admin_buildingconsents_team@xyzmunicipality.com etc. In these situations all the admin team share and use that email_id for attending to and answering the queries and sending prilimenary acknowledgement of the receipt of information etc.
I am assuming a situation like that.
You can create two tables.
One table 'A' as (Emp_Id, Email, Sex, Address, race and nationality) and
other 'B' as (NonEmail_Id, email_shared_with_emp_id);
Create a foreignkey using emp_id column in both the tables.
Then you will be able to link both the tables using a.emp_id=b.emp_id in the where clause.
I am thinking you can use this data structure and solve your problem.
Good Luck.
Rao
8/31/09