CODEWITHSUNDEEP
mysqlsql
user14461410
user14461410

Reputation: 31

Inner Join not matching accurately

I have two tables which I am trying to join on a matching ID.

The format is usually a simple number, like 16.

However, in one of the tables some IDs are longer and contain letters as well and can end up looking like this: 16AfGs9.

I would think, okay, not a problem. However, my MySQL query is currently matching the ID 16 row to all IDs having 16xxx format.

Is it possible to have the INNER JOIN on an exact match?

The current query:

select * from t1 
inner join t2 
on t1.id=t2.customer_id

Upvotes: 0

Views: 541

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You need to do a string comparison. The column is clearly a string, so the comparison needs to be to a string:

where id = '16'

If you have ids in different tables and one is an integer and one a string, then you need to fix your data model! In the meantime, you can convert to a string:

where cast(inttable.id as char) = stringtable.id

Upvotes: 1

Related Questions