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
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
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.