CODEWITHSUNDEEP
mysqlsqljoinkey
Lutfi
Lutfi

Reputation: 131

Joining two tables with similar columns but different values

First of all, I have seen this question before : (Two tables with similar columns but different primary keys)

But there is a slight difference from my problem.

My table looks something like this : 

Table 1
ID     Name    Salary
123    Mike    5000
129    David   4000
133    Linda   4500

Table 2
ID      Dept
0123    IT
0129    Tech Support
0133    Analytics

I want to join these two tables based on ID so that I can see their ID, Name, and Dept. ID in Table 1 and Table 2 is for the same person in actuality, but there is a 0 in front of the ID in Table 2 so the program treats it as unique value giving me result something like this :

Table result:
ID     Name    Dept
0123   null    IT
0129   null    Tech Support
0133   null    Analytics
123    Mike    null
129    David   null
133    Linda   null

Is there a way that I can remove the "0" from ID in Table 2 or join them somehow? They don't have other similar columns that can be used. It was supposed to be like this : 

Table result
ID     Name    Dept
123    Mike    IT
129    David   Tech Support
133    Linda   Analytics

Thank you and sorry if my question is a duplicate, I can't find one similar like mine.

Upvotes: 0

Views: 46

Answers (1)

Nick
Nick

Reputation: 147166

If the last 3 digits of the ID in Table 2 are the ID value that can be found in Table 1 then you can just take the Table 2.ID value modulo 1000 (to strip any leading digits) and JOIN on that:

SELECT t1.ID, t1.Name, t2.Dept
FROM `Table 1` t1
JOIN `Table 2` t2 ON t2.ID % 1000 = t1.ID

Output:

ID      Name    Dept
123     Mike    IT
129     David   Tech Support
133     Linda   Analytics

Demo on dbfiddle

Upvotes: 1

Related Questions