CODEWITHSUNDEEP
sqloracle-database
user3872094
user3872094

Reputation: 3351

Query returning same row twice as result

I'm learning how to write sql joing and wrote the below Query.

SELECT patient.name, patient.address, patient.CONTACTNUMBER, patient.disease,
patient.joining FROM patient INNER JOIN add_doctor ON patient.DOCASSIGN = 4534543

Here the result I'm getting is 

**NAME  ADDRESS     CONTACTNUMBER   DISEASE JOINING**
sdfdvd  sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15
sdfdvd  sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15

I'm confused on why there are two same rows returned as my query result. please let me know how can i fix it and also plese help me understand why it is printing the same row twice

patient Table

ID       NAME         DISEASE    ADDRESS    CONTACTNUMBER   DOCASSIGN   ROOMASSIGN  JOINING    DISCHARGE
5656    sdfdvd       sdvdvbd    sdfdfbdfgd       3243453    4534543      General    01-JUL-15   15-JUL-15
65654   rgdgdgvds   sdfdfdgdfdb 1123fdffdbfd    4556767      4545            VIP    08-JUL-15   15-JUL-15

Doctor Table

ID          NAME    ADDRESS        EMAIL    CONTACT       SPECIALITY    SALARY  PASSWORD
4534543 fggfhdfs    rgghfgjfdj  gfhgfhjg    4365457567  dfsfhgjgkgfk    435435  safgdgd
4545    aaaaaaaaa   dffgdsg       fdfgds    43535       dffssgdhgd      324234  dfdhgggfdh

Upvotes: 1

Views: 6477

Answers (2)

Tobias
Tobias

Reputation: 19

The table "patient" you are querying contains at least two rows containing entries of the same values for name, address, CONTACTNUMBER, disease, joining. This is actually a data integrity problem. The patient table should not contain these two rows. If you like to show only one row in the result, you can do this by using distinct()

SELECT distinct(patient.name), 
       patient.address, 
       patient.CONTACTNUMBER, 
       patient.disease,
       patient.joining 
FROM patient INNER JOIN add_doctor 
    ON patient.DOCASSIGN = 4534543

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

In TSQL an INNER join is evaluated in 2 logical phases

  1. Cross join
  2. Filter out rows which do not satisfy filter condition in ON clause.

To say if table Patient has row like:

NAME    ADDRESS     CONTACTNUMBER   DISEASE JOINING   DOCASSIGN
dfdvd   sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15 4534543

And table add_doctor has rows like:

DOCNAME
Test   
Test2

so for inner join first a cross join will be done with interim results as:

Step1: patient Cross JOIN add_doctor

This will result in 1*2 = 2 rows as:

NAME    ADDRESS     CONTACTNUMBER   DISEASE JOINING   DOCASSIGN DOCNAME 
dfdvd   sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15 4534543   Test    
dfdvd   sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15 4534543   Test2

In second logical phase of Filerting based on ON clause as both the rows qualifies the filter condition 

Step2: ON patient.DOCASSIGN = 4534543

the final result has two rows with duplicate data:

NAME    ADDRESS     CONTACTNUMBER   DISEASE JOINING
sdfdvd  sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15
sdfdvd  sdfdfbdfgd  3243453         sdvdvbd 01-JUL-15

To remove duplicate data use DISTINCT keyword and re-write the query as:

SELECT DISTINCT p.name,
                p.address, 
                p.CONTACTNUMBER, 
                p.disease,
           p.joining 
FROM patient as p 
INNER JOIN add_doctor a ON p.DOCASSIGN = 4534543

You can refer to the different logical phases in which a query is processed here...

Upvotes: 1

Related Questions