I've these data :
table of possible combinations between Id and Code :
| Id | Code |
|---|---|
| A | 184 |
| A | 2394 |
| A | XAE48R585 |
| A | 23450403 |
| A | 140 |
| A | GDAL50000 |
| B | 45654 |
| B | 3524 |
| B | CJA3454224 |
| B | LLSZ00087 |
| B | 540 |
In one other table i have for one id its first code. In this example for id A i've the code XAE48R585 and for id B i've the code CJA3454224.
Referential table :
| Id | code1 |
|---|---|
| A | XAE48R585 |
| B | CJA3454224 |
But one Id have a second code that match with the first code. For the id A his second code is 140 and for id B it's 540. The global rule is that for a second code is on 3 digit and end by 0 for each Id. And this is the case for several Id. So i would like to create a mapping like this
Mapping table:
| code1 | code2 |
|---|---|
| XAE48R585 | 140 |
| CJA3454224 | 540 |
Thanks a lot for your help
CodePudding user response：
its easy to in sql using mysql self join.
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;