query show 0 record from two tables have same data on join

Question

'm filtering records from 2 tables users and temp_bach_users_registeration on the base of email both have some same emails but in query record is null but when i individually search from both they show me records

in temp_bach_users_registeration table i import records from CSV is there this issue is because of csv ??

Query:

SELECT
users.email,
temp_bach_users_registeration.email
FROM
users, temp_bach_users_registeration 
WHERE users.email = temp_bach_users_registeration.email

users table is MyISAM and temp_bach_users_registeration is InnoDB storage engine .

DB Structure of users table : enter image description here ........

DB Structure of temp_bach_users_registeration table : enter image description here ..........

can somebody help me to figure out this issue .

thanks .

Do both email columns use the same character set / collation? — wolfgangwalther, Oct 30 '14 at 10:02
yes both have same character set / collation but storage engines are different. — Haseeb, Oct 30 '14 at 10:05
@Haseeb - you respond to questions but fail to provide the requested sample data. Without sample data, I doubt you'll get an answer soon. — Lieven Keersmaekers, Oct 30 '14 at 10:19
http://dbaspot.com/databases/399510-strange-problem-mysql-string-comparison.html seems to be related. `WHERE STRCMP(users.email,temp_bach_users_registeration.email) = 0` could solve the issue. — wolfgangwalther, Oct 30 '14 at 10:46
probably during the importation, the email addresses kept the char " from the begin to the end as field separator — Gianluca Colombo, Oct 30 '14 at 11:06
@Strawberry Well, apparently it is not :) http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join — wolfgangwalther, Oct 30 '14 at 11:35
@Strawberry I have to admit, that my mental query optimizer was not working as effectively as the real one ;) — wolfgangwalther, Oct 30 '14 at 11:42

Haseeb · Accepted Answer · 2014-10-30T11:31:08.710

1

finally i got the issue. there is \r in end of value of column emails in temp_bach_users_registeration table but it not shows in phpMyadmin .

i find this by searching again with this query .

SELECT *
FROM `temp_bach_users_registeration`
WHERE `email` IN('test@yahoo.com\r')
LIMIT 0, 1000

and got the records.

edited Oct 30 '14 at 11:31

answered Oct 30 '14 at 11:22

Haseeb

2,214
1
22
43

That's rather random. :-( – Strawberry Oct 30 '14 at 11:40

score 0 · Answer 2 · answered Oct 30 '14 at 09:20

0

try in this way

SELECT
users.email,
temp_bach_users_registeration.email
FROM
users, temp_bach_users_registeration 
WHERE trim(users.email) = trim(temp_bach_users_registeration.email)

answered Oct 30 '14 at 09:20

Gianluca Colombo

717
17
38

same result no record frond . :( – Haseeb Oct 30 '14 at 09:34
very very strange... can you post a screenshot of the tables or something like that? Which kind of ODBC driver are you using or kind of Sql explorer?! What about email fields? Text or Char? – Gianluca Colombo Oct 30 '14 at 09:37
both are varchar(100) and collection is utf8_general_ci – Haseeb Oct 30 '14 at 09:51

query show 0 record from two tables have same data on join

2 Answers2