• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Query

 
naresh babu
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have 2 tables:

Employee - id, phonenumber

Department - id, empType, active

I want to retrieve all the employees who have duplicate phone number in employee table. For each group of duplicate
phone number , i want to check if empType are different. If empType are different and active=false - get me
that data.




Ex: Employee
1, 234-123-123
2 , 234-123-1234
3 , 121-121-1212
4, 121-121-1212
5, 111-111-1111
6, 222-222-2222
7, 222-222-2222

Department
1, 1000, false
2, 1000, false
3, 1001, false
4, 1002, false
5, 10003, false
6, 10004, true
7, 2000, false
8, 20001, false

Output should be :
3, 1001, false, 121-121-1212
4, 1002, false, 121-121-1212
6, 2000, false, 222-222-2222
7, 20001, false, 222-222-2222


Can i achieve this is in a single Query?


Thanks! for your time.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And what's the link between employee and department?
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naresh babu wrote: Employee - id, phonenumber
Department - id, empType, active

I think id is primary key and one to one relation between employee and department. Even I'm trying to solve but not succeeded yet. Need to start studying sql
 
naresh babu
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! for the response. Both employee and department are linked with ID .
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naresh babu wrote:Thanks! for the response. Both employee and department are linked with ID .

So the id of the department table is the id of the employee?

Back to the query now: what did you already have tried?
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naresh babu wrote:Both employee and department are linked with ID
yes we got that but id is primary key of employee or department? what is relation between these two tables? One to one, One to many etc. ?
 
naresh babu
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, Roel. ID is the primary -foreign key relation with employee and department tables.

I couldn't retireve in single query , hence created stored procedure which will
Step 1)Query List all duplicate phone numbers with ids and write to file
Step2) From above list, check the records for diff empType.

Instead of stored proc,i am looking if this can be achieved in single Query instead of procedure.


 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naresh babu wrote:Output should be :
3, 1001, false, 121-121-1212
4, 1002, false, 121-121-1212
6, 2000, false, 222-222-2222
7, 20001, false, 222-222-2222

According to me this output is incorrect according to the requirements you have listed.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naresh babu wrote:Instead of stored proc,i am looking if this can be achieved in single Query instead of procedure.

I also don't know if it can be achieved with a single query. You will only know if you try. And if you are stuck, you can show the query and mention with issue(s) you are currently experiencing
 
naresh babu
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! Roel for the responses. Will try out and update incase of issues.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, here's one way to test this, using CTEs:

They just supply the data and are not required, though it makes testing for the rest of us a bit easier. For anyone using something other than Oracle, remove the " FROM Dual" lines.

To start , i see 2 steps. First, finding duplicates with multiple emptypes. Then, displaying the data where active is false. The purpose of the bifurcation is while the identification is group related, the displayed data is not the group, but the members of that group, which is a bit more difficult and complicated as one step.

For the first step, consider using a GROUP BY/HAVING to identify the groups of similar phone number with multiple emptypes. This can be a CTE or a subquery in the FROM clause. The second step would simply join the phonenumber back to employee, and then department, to retrieve the desired information while filtering on active.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic