Home » SQL & PL/SQL » SQL & PL/SQL » SQL Statement - Complex Conditions (10.1.3)
SQL Statement - Complex Conditions [message #418633] Mon, 17 August 2009 23:17 Go to next message
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 #418634 is a reply to message #418633] Mon, 17 August 2009 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: SQL Statement - Complex Conditions [message #418643 is a reply to message #418633] Mon, 17 August 2009 23:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Does your EMPLOYEE table contains multiple records with same email?
If not, then it must return only one row for specified email address as login id, as all the email addresses in the world are unique.

regards,
Delna
Re: SQL Statement - Complex Conditions [message #418648 is a reply to message #418643] Tue, 18 August 2009 00:04 Go to previous messageGo to next message
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 #418649 is a reply to message #418633] Tue, 18 August 2009 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Duplicates are where COUNT(*) > 1
Re: SQL Statement - Complex Conditions [message #418650 is a reply to message #418633] Tue, 18 August 2009 00:18 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Keep only one record for one email address in your first employee table. Remove duplicates. Simple!
No need to create new table.

regards,
Delna
Re: SQL Statement - Complex Conditions [message #418669 is a reply to message #418650] Tue, 18 August 2009 01:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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 #418918 is a reply to message #418914] Wed, 19 August 2009 03:13 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Sabine,

hahaha, really sorry if again my sentences get you all confuse.

I think better I give you an example:

Employee A is the employee who has valid email address and will login to the application. Once she is login, the application will require her email address to query her records for the usage of the subsequences function

Employee B, C and E are those employees have no email address, but in the HRMS system, there is email columns require to be key in and the email address must be an valid email address (for other purposes).

Hope it is cleared Smile

Ying
Re: SQL Statement - Complex Conditions [message #418919 is a reply to message #418918] Wed, 19 August 2009 03:19 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay, I understand what you are writing. However, what I still don't see is what the issue is. What is the problem you are trying to solve?
Re: SQL Statement - Complex Conditions [message #418921 is a reply to message #418919] Wed, 19 August 2009 03:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #418933 is a reply to message #418931] Wed, 19 August 2009 04:10 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
You can't uniquely identify a record in the table based on a field which is not unique...
Re: SQL Statement - Complex Conditions [message #418940 is a reply to message #418633] Wed, 19 August 2009 04:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #418957 is a reply to message #418918] Wed, 19 August 2009 05:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Either fix your HRMS application so no dummy email-addresses are entered or use different data as identifiers.
Re: SQL Statement - Complex Conditions [message #418958 is a reply to message #418931] Wed, 19 August 2009 05:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Smile
As its all about how you store data and interpret it Wink

[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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Wink

[Updated on: Thu, 20 August 2009 00:16]

Report message to a moderator

Re: SQL Statement - Complex Conditions [message #419145 is a reply to message #419129] Thu, 20 August 2009 01:18 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Due to the email for every employee must be a valid email (for other purposes) so we can't do that.

Seems like a lot of possibilties have been raised and though of but not possible to do. A bit upset.

Ying

[Updated on: Thu, 20 August 2009 01:19]

Report message to a moderator

Re: SQL Statement - Complex Conditions [message #419150 is a reply to message #419145] Thu, 20 August 2009 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: SQL Statement - Complex Conditions [message #419151 is a reply to message #419145] Thu, 20 August 2009 01:45 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Yes If you choose a BAD design in the beginnning itself..you have to take the overhead of maintaining it
Re: SQL Statement - Complex Conditions [message #419152 is a reply to message #419151] Thu, 20 August 2009 02:16 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

No, your didn't upset me. I really appreciate the advise. I just upset myself can't get my problem solved.

Thank you.

Ying
Previous Topic: Execute immediate inside a Loop (Merged 4)
Next Topic: Empty table takes time to show count
Goto Forum:
  


Current Time: Fri Dec 09 00:16:34 CST 2016

Total time taken to generate the page: 0.13283 seconds