CODEWITHSUNDEEP
mysqlsqljoinvarchar
DigitalMusicology
DigitalMusicology

Reputation: 251

INNER JOIN on VARCHAR

The following query returns 0 rows, even though the two tables contain several matching values.

SELECT i.isrc, m.isrc FROM table1 i INNER JOIN table2 m ON i.isrc = m.isrc;

Because the isrc column is a varchar type, I suspected it might be a problem with the table collation. Changing both collations to utf-8, however, did not fix the problem.

What could be the problem here?

Upvotes: 0

Views: 8326

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Take a look at what you think are matching values in each table:

select src, sum(in1) as in1, sum(in2) as in2
from ((select distinct i.isrc as src, 1 as in1, 0 as in2 from table1 i) union all
      (select distinct m.isrc as src, 0 as in1, 1 as in2 from table1 m)
     ) t
group by src;

Before doing the above, you might find a summary helpful:

select in1, in2, count(*), min(src), max(src)
from (select src, sum(in1) as in1, sum(in2) as in2
      from ((select distinct i.isrc as src, 1 as in1, 0 as in2 from table1 i) union all
            (select distinct m.isrc as src, 0 as in1, 1 as in2 from table1 m)
           ) t
      group by src
     ) s
group by in1, in2;

The semantics for being the same for a group by and being the same for a comparison might not be the same (spaces at the end will affect similarity for group by but shouldn't affect similarity for equality, for instance). However, this will let you know what could be matching across the two tables.

Upvotes: 0

Nadeem_MK
Nadeem_MK

Reputation: 7689

Try the below, 

SELECT i.isrc, m.isrc 
FROM table1 i 
INNER JOIN table2 m
ON UPPER(TRIM(i.isrc)) = UPPER(TRIM(m.isrc))

NOTE:

  1. Trim the values on both sides
  2. Normalise the case

Upvotes: 3

Related Questions