| SQL Statement - Complex Conditions [message #418633] |
Mon, 17 August 2009 23:17  |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
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 compute a SQL statement base on the condition below:
1.) Table A consists of employee data (Emp_Id, Email, Sex, Address, race and nationality)
We use email as login, 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.
Hope my explaination won't be confused.
Please advise.
Ying
|
|
|
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418648 is a reply to message #418643] |
Tue, 18 August 2009 00:04  |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi,
Really great to have reply. Yes, my EMPLOYEE table contains multiple records with same email. This is the main problem cause the sytem coudn't uniquely grab the correct data.
Not many employees's email address is shared, only 30 employee out of 1000++ employees, so what I have to do is to separate them out into diff table so that can indicate them correctly.
Ying
|
|
|
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418669 is a reply to message #418650] |
Tue, 18 August 2009 01:19 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi Delna,
No, we can't remove the duplciation as we have purposes to use same email address.
Now, we just wish to make sure that the data of employees that we grab is really belong to that particular employee.
So, the Table B that I created will has two main fields, they are "Emp_id, Creation Date". If the employees email is shared (COUNT(*) > 1), then the employee's Emp_Id will be in Table B.
Now,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.
Ying
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418686 is a reply to message #418669] |
Tue, 18 August 2009 02:37 |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
|
Well, what would be your definition for who is the "correct employee"? If you use the email address as the login name in your application there would be several users with the same login name with your data... So which algorithm do you suggest to use for finding out which employee is the one logging in? You'll have to find/add some unique value to identify which employee you want the details for.
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418692 is a reply to message #418669] |
Tue, 18 August 2009 03:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your solution is overly complex and will cause more problems than it's worth.
If the email id isn't a unique identifier for an employee then stop pretending that it is.
You need to find another way of identifying an employee - would Email address and date of birth, or enmail address and surame work?
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418711 is a reply to message #418692] |
Tue, 18 August 2009 04:48 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi,
Ok, I think I need to further elaborate to make it clear.
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.
Regards,
Ying
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418714 is a reply to message #418711] |
Tue, 18 August 2009 05:17 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In which case, as you have only the email address to identify a user, all the users who share the same email address will be able to log into the same account.
Unless you can capture some more information in addition to the email address, you won't be able to distinguish between these users.
Do your users have specific computers that they use - you might be able to capture the IP address that they're logging in from for example.
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418898 is a reply to message #418714] |
Wed, 19 August 2009 02:01 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi JRowbottom,
In fact, those employee without email address will not going to login to the system. But, in the Human Resource System, an email field is required to fill in, so no choice. By the way, is that possible to compute the SQL base on the condition I want?
Ying
[Updated on: Wed, 19 August 2009 02:06] Report message to a moderator |
|
|
|
| Re: SQL Statement - Complex Conditions [message #418914 is a reply to message #418898] |
Wed, 19 August 2009 02:55 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Ying,
If you are 100% sure that no employee with one of those "shared" e-mail addresses will ever attempt to login, then why do you care about those records?
I mean, what is the issue you are trying to solve? It looks to me that you either
(a) want a list of all users that share e-mail addresses (answer was given earlier on, count(*) > 1)
or
(b) you want to get employee data for a given e-mail address (login name). In this case, if no "shared e-mail users" ever login, then there is no issue because you will get a unique record per any given e-mail address/user.
Or is there any other option that I overlooked?
- Sabine
|
|
|
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418921 is a reply to message #418919] |
Wed, 19 August 2009 03:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do you have any way of identifying these non-logging-in users?
You mention in your OP that you have a table B that contains details of employees whose email addresses are shared -
In your example, assuming that A has their email address shared with B,C & E which of the following sets of employees would appear in table B:
{A}
{B,C,E}
{A,B,C,E}
This would be so much easier if you could come up with a test case - then we wouldn't have to drag each piece of information out of you .
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418930 is a reply to message #418921] |
Wed, 19 August 2009 04:00 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
I have no way of identifying those users and the number of the employees are about 200.
In your example, assuming that A has their email address shared with B,C & E which of the following sets of employees would appear in table B:
{A}
{B,C,E}
{A,B,C,E}
In fact, the table B only contain the employee ID and email address of the employees whose email adddresses are shared. So base on my example, the Employee A ID and email ID will be in the table B, as below:
Emp Id Email
100100 employeeA@mydomain.com
The table B is the table I create separately in order to solve my problem, but out of sudden, I realise that I have problem in compute the SQL statement.
Ying
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418931 is a reply to message #418919] |
Wed, 19 August 2009 04:07 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
To answer skooman, the issue 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 - -
Thanks for willing to understand my problem
Ying
[Updated on: Wed, 19 August 2009 04:10] Report message to a moderator |
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418940 is a reply to message #418633] |
Wed, 19 August 2009 04:53 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
| Quote: | 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.
|
Just add a flag column to your table A that shows that it is Original...e.g. if
-----------------------------
employee | email ID | flag
-----------------------------
ayush | a@a.com | 1
chiku | a@a.com | 0
xyz | a@a.com | 0
Surely you can find that particular employee who's email is shared by other employee
Here flag 1 means original ID
and 0 means shared ID
In Simple words It depends on how you store your data and interpret it
[Updated on: Wed, 19 August 2009 04:59] Report message to a moderator |
|
|
|
| Re: SQL Statement - Complex Conditions [message #418951 is a reply to message #418940] |
Wed, 19 August 2009 05:19 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Thanks ayush_anand,
Why I can be in such difficult situation is due to we can't alter the Original table structure to have the "Flag". That is why I have to create my own table - Table B for this purpose.
Ying
|
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418958 is a reply to message #418931] |
Wed, 19 August 2009 05:40 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Assuming that table B holds the emp_id and email address of the users that you want, then you should be able to do:SELECT <columns>
FROM A
WHERE email_address = <address>
AND A.emp_id IN (SELECT b.emp_id
FROM B
WHERE b.email_address = a.email_address)
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #418961 is a reply to message #418958] |
Wed, 19 August 2009 05:58 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
yes in table B have only those users that dont have email Ids & use other person's email Ids.And also try to see if you can match part of the email Id with the name of the employee.
| Quote: | Why I can be in such difficult situation is due to we can't alter the Original table structure to have the "Flag". That is why I have to create my own table - Table B for this purpose.
|
As you can create table you can do anything 
As its all about how you store data and interpret it 
[Updated on: Wed, 19 August 2009 06:02] Report message to a moderator |
|
|
|
| Re: SQL Statement - Complex Conditions [message #419128 is a reply to message #418958] |
Thu, 20 August 2009 00:07 |
ying
Messages: 143
Registered: May 2002
|
Senior Member |
|
|
Hi,
Yes, correct, I can do that by have only those users that dont have email Ids & use other person's email Ids in Table B but when come to maintainance it is tedious. This is because the Table B is the table that I created and maintain manually. There are about 200++ of employee have no email address and If there has any new employee join in and have data in the HRMS, the table B won't have the new employee record.
That is why, I think of seprating out those employee whose email address is shared by other.
I think I can write my logic as below:
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 EMPLOYEE_MASTER_TABLE
But, have stuck on how to form the SQL>
Ying
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #419129 is a reply to message #419128] |
Thu, 20 August 2009 00:14 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
| Quote: | There are about 200++ of employee have no email address and If there has any new employee join in and have data in the HRMS,
|
I think you should correct your HRMS system in that case for just not allowing people to enter without email_ids or provide some dummy Ids in that case like a@a.com so that they are easily identificable.
So Whenever a new employee in future joins you dont have to maintain table B at all(as it contains employees that use email Ids of others)
| Quote: | That is why, I think of seprating out those employee whose email address is shared by other.
|
In that case also you have to maintain the other table when a new employee joins
[Updated on: Thu, 20 August 2009 00:16] Report message to a moderator |
|
|
|
|
|
| Re: SQL Statement - Complex Conditions [message #419150 is a reply to message #419145] |
Thu, 20 August 2009 01:43 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You have a system with a stupid constraint for the way you use it (the email address is mandatory, even though you don't know it for each person).
We tell you to fix that, instead of working your way around it creating problems further down the road, and you tell us that is impossible.
Sorry we upset you..
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
