Working with a legacy table.

Can a column be parsed into tokens before the join or select is made to fetch the association (based on said tokens)?

If we have a STATUS column = 07000101, that corresponds to the IH International Hub row in the lookup table. See how the 07 00 01 01 line up with the Type L1, L2, and L3 columns?

This seems to be a Join Strategy we want to introduce. Did not find anything out there, or know the right terms to use while searching for it.


mysql> select * from lookups where type = 7;
+------+----+----+----+------+-----------------------+--------------------------------------+
| Type | L1 | L2 | L3 | Name | Description |
+------+----+----+----+------+-----------------------+--------------------------------------+
| 7 | 0 | 0 | 0 | NULL | Source |
| 7 | 0 | 1 | 0 | NULL | Hub |
| 7 | 0 | 1 | 1 | IH | International Hub |
| 7 | 0 | 1 | 2 | EWRH | EWRH Airport Hub |
| 7 | 0 | 1 | 3 | OAKH | OAKH Airport Hub |


Thanks!

Resolution was to create a view that concatenates the rows:

the interesting part...

VIEW `lookups_view` AS select `lookups`.`Type` AS `Type`,`lookups`.`L1` AS `L1`,`lookups`.`L2` AS `L2`,`lookups`.`L3` AS `L3`,`lookups`.`Name` AS `Name`,`lookups`.`Description` AS `Description`,`lookups`.`rowguid` AS `rowguid`,((((`lookups`.`Type` * 1000000) + (`lookups`.`L1` * 10000)) + (`lookups`.`L2` * 100)) + `lookups`.`L3`) AS `lookup_value` from `lookups`

Resolution was to create a view that concatenates the level columns into one new column, lookup_value:

the interesting part...