Clever Geek Handbook

Mysql inner join returns duplicate rows

Hi Developers I have two tables joining through mysql connection getting multiple rows instead of one row please solve this problem,

tbltestdefault
-----------------------------------------------
ID   Test_ID           Description
-----------------------------------------------
1    117                  Blood Group   
2    117                  Rh Factor
3     4                   HB
4     4                     RBC
5     4                     ESR

tblreportdetail
-----------------------------------------------
ID   Test_Default_ID           Result_Value
-----------------------------------------------
1     117                               A
2     117                              Positive
3      4                              12
4      4                                15
5      4                                25

my request

SELECT a.Description,
b.Result_Value
from tbltestdefault a
inner join tblreportdetail b on a.Test_ID  = b.Test_Default_ID

this query returns multiple rows

the result of the above query

tblreportdetail
Description    Result_Value
HB          12
HB          15    
HB          25
HB          12
RBC         15
RBC         25
RBC         12
RBC         15
ESR         
ESR
ESR
ESR
Blood Group   
Blood Group   
Blood Group   
Blood Group   
Rh Factor
Rh Factor
Rh Factor
Rh Factor
+3


source to share


2 answers


They don't cheat. You get exactly what you asked for. If you add two table IDs to your query, you can see that they don't cheat, for example.



Description    Result_Value   a.id   b.id
HB             12             3      3
HB             15             3      4
HB             25             3      5
HB             12             4      3
HB             15             4      4
HB             25             4      5
etc...
+2


source


Q: the question arises, why am I getting duplicate lines?

A: Rows with the same content are generated in the result set by query because of the specified JOIN predicate and because there are multiple rows in each table that have the same Test_ID

/ Test_Default_ID

.

Given the data shown, the expected query result will be 13 rows. This is 2 * 2 + 3 * 3.

Each of two Test_ID

= 117 rows corresponds to two Test_Default_ID

= 117 rows, for a total of 4 rows returned.



Each of three Test_ID

= 4 lines corresponds to three Test_Default_ID

= 4 lines, for a total of 9 lines returned.

Because the query does not contain unique row identifiers in the result set, the contents of some of the returned rows are identical.

This is why you are seeing "duplicate rows" returned by the query.

+1


source






More articles:


All Articles