OraFAQ Forum: SQL & PL/SQL » Query to find duplicate rows from table

Home » SQL & PL/SQL » SQL & PL/SQL » Query to find duplicate rows from table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to find duplicate rows from table [message #624531]

Tue, 23 September 2014 07:17

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Team,

We need your help in getting duplicate rows from query. Mapping Table will have old and New employee ID's and trasaction table like JOB will have the details like Employee ID, Name, Address Etc. (Employee ID is Key field in Job table). Need a query to fetch "only duplicate rows" from job table

Create two tables, Mapping and Job :

SQL> CREATE TABLE EMPL_MAPPING (EMPLID_OLD VARCHAR (11), EMPLID_NEW VARCHAR (11)) ;

Table Created.

SQL> CREATE TABLE JOB (EMPLID VARCHAR (11), NAME VARCHAR (30) , ADDRESS VARCHAR (30));

Table Created.

Insert rows into Mapping table :

SQL> INSERT INTO EMPL_MAPPING VALUES ('12348', 'ABC48');

1 row created.

SQL> INSERT INTO EMPL_MAPPING VALUES ('12349', 'ABC49');

1 row created.

SQL> INSERT INTO EMPL_MAPPING VALUES ('12345', 'ABC45');

1 row created.

SQL> INSERT INTO EMPL_MAPPING VALUES ('12346', 'ABC46');

1 row created.

Insert rows into Job table :

SQL> INSERT INTO JOB VALUES ('12345', 'TEST5', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('12346', 'TEST6', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('12347', 'TEST7', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('12348', 'TEST8', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('12349', 'TEST9', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('ABC48', 'TEST8', 'FL');

1 row created.

SQL> INSERT INTO JOB VALUES ('ABC49', 'TEST9', 'FL');

1 row created.

Query output should look like:

SQL> select * from JOB ;

EMPLID      NAME                           ADDRESS
----------- ------------------------------ ------------
12348       TEST8                          FL
12349       TEST9                          FL
ABC48       TEST8                          FL
ABC49       TEST9                          FL

4 rows selected.

Report message to a moderator

Re: Query to find duplicate rows from table [message #624539 is a reply to message #624531]

Tue, 23 September 2014 07:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Need a query to fetch "only duplicate rows" from job table

Can you define what is "duplicate rows" for you in this case.

Note: thanks for the test case but post the just the code not its execution as we can just copy ans paste it to reproduce what you did.

Report message to a moderator

Re: Query to find duplicate rows from table [message #624542 is a reply to message #624539]

Tue, 23 September 2014 07:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this the result you want (but for the format):

SQL> select j1.*, j2.emplid
  2  from job j1, job j2, EMPL_MAPPING e
  3  where j1.emplid = e.emplid_old
  4    and j2.emplid = e.emplid_new
  5  /
EMPLID      NAME                           ADDRESS                        EMPLID
----------- ------------------------------ ------------------------------ -----------
12348       TEST8                          FL                             ABC48
12349       TEST9                          FL                             ABC49

Report message to a moderator

Re: Query to find duplicate rows from table [message #624548 is a reply to message #624542]

Tue, 23 September 2014 08:03

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Hi Michel..

With duplicate i mean, data having old employee ID as well as New employee Id.
When we query Job table, in the results we should get 4 rows (2 for each employee with old employee ID row and new employee ID row)

Regards,
Madhu

Report message to a moderator

Re: Query to find duplicate rows from table [message #624555 is a reply to message #624548]

Tue, 23 September 2014 08:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

But the result (ignore the format) is the correct one you want.
You just want to compare emplid and not name and adresses.
I mean if you have:

EMPLID      NAME                           ADDRESS
----------- ------------------------------ ------------
12348       TEST8                          FL
ABC48       TESTXXX                        CA

Are they duplicates or not?

Report message to a moderator

Re: Query to find duplicate rows from table [message #624564 is a reply to message #624555]

Tue, 23 September 2014 08:39

gaganmadhu
Messages: 11
Registered: April 2008

Junior Member

Yes, We just want to compare EMPLID only and nothing else. In your above example, they are still duplicates as emplid is repeated (having old and new).

Report message to a moderator

Re: Query to find duplicate rows from table [message #624566 is a reply to message #624564]

Tue, 23 September 2014 08:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Yes, We just want to compare EMPLID only and nothing else.

So my query gives the correct result (forget the format you can just add the missing columns and split each rows into 2).
What is your Oracle version (this is mandatory with all questions).

[Updated on: Tue, 23 September 2014 08:42]

Report message to a moderator

Previous Topic:	what is the issue in my query
Next Topic:	which column to have index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:49:22 CDT 2024