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 AWinWin
