CODEWITHSUNDEEP
sqloracle-database
SHOWMIK SETTA
SHOWMIK SETTA

Reputation: 27

simple sql query respect to 2 column and single table

table name doctor_dtl

column 1 name doc_code
column 2 name doctor_name

problem: same doc_name are inserted multiple time and multiple doc_code are generated against that all same name so doctor name duplicated but there doc_code are different i want a query where i can display all duplicate doctor name and all doctor code? example: 

         john doc0001
         john doc0010
         amit doc0006
         amit doc1000
         amit doc1010

i want this type of output to be display

Upvotes: 0

Views: 185

Answers (3)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use it.

SELECT 
    doc_code,
    doctor_name 
FROM doctor_dtl
WHERE doctor_name IN (
    SELECT 
    doctor_name
    FROM doctor_dtl
    GROUP BY doctor_name
    HAVING COUNT(*) > 1)

Sql Fiddle

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Here is one approach if you are working SQL Server

SELECT doc_code,
       doctor_name
FROM
(
    SELECT doc_code,
           doctor_name,
           COUNT(doctor_name) OVER(PARTITION BY doctor_name) [Count]
    FROM <table_name>
) a
WHERE [Count] > 1;

Result :

doc_code    doctor_name
doc0006     amit
doc1000     amit
doc1010     amit
doc0001     john
doc0010     john

Upvotes: 1

Gaurav
Gaurav

Reputation: 1109

select a11.doctor_name, a11.doc_code 
from doctor_dtl a11 join (select
doctor_name, count(doctor_name)  from doctor_dtl Group by doctor_name having
count(doctor_name) > 1  ) a12 on a11.doctor_name = a12.doctor_name group
by a11.doctor_name, a11.doc_code

Upvotes: 0

Related Questions