mysql - SQL JOIN question (yet another one) -

sounds simple i'm stuck

table                 table b  col_a  col_b          col_a  col_c 1      b              1      c 2      c              2      d 3      z              3      4      d              4      e 33                  5      k                       6      l                       33     b                       33     b 

i want join table b:

select * inner join b on a.col_a = b.col_a 

i expecting 5 records result.

expected join result         ** actual result **  col_a  col_b col_c col_x[n]...       col_a col_b col_c  col_y[n]... 1      b     c     ...                   1     b     c  ... 2      c     d     ...                   2     c     d  ... 3      z         ...                   3     z      ... 4      d     e     ...                   4     d     e  ... 33         b     ...                   33        b  ...                                          33        b  ... 

why did mysql match 33 twice? because 2 values 33 in table b.

what want though, 1 record same value in col_a. how do that?

edit: updating tables' design include more columns contain non-identical data, because them being posed more questions solved problems. anyway, answer use group by, performance penalty huge, on table contains above 50 million records (and growing). however, best approach solve problem use compound statement (using union all) every distinct value in col_a. performance benefit x5 ~ x10 faster !!

you have 33 twice in table b.

either select distinct or group col_a, ...:

select distinct *      join    b on ( a.col_a = b.col_a ) ; 

or

select    *        join      b on ( a.col_a = b.col_a ) group  col_a, col_b, col_c ; 

you should clean table, though. depending on how many occurrences of repeated row, might faster use subquery:

select  *      join    (select distinct * b) c         on ( a.col_a = c.col_a ) ;