0

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
Lutfi
  • 131
  • 7

1 Answers1

1

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

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Okay thanks. What if the number in front is different? Like 123 = 60123 124 = 60124 133 = 60133 The 60 is prefixed that should be excluded (it is to check they are at which region), but it actually refers to the same person. Thanks – Lutfi Mar 31 '20 at 03:44
  • @Lutfi are they always 3 digits and 4 digits?, or just always 1 more digit in `Table 2`? Or ??? – Nick Mar 31 '20 at 03:46
  • Actually, the number in front represents a region (60 is another region, 61 is another region), so the real ID is the last 3 digits. So, 60123 = 123, 60 is region A Sorry if I didn't make it clear. – Lutfi Mar 31 '20 at 03:48
  • 1
    @Lutfi no problem. See my edit, that should do what you want – Nick Mar 31 '20 at 03:51