Make an alias column from 2 different tables in MySQL

Question

I have a database table which has correlation with 2 different tables, for example:

   === inventory ===
+------------+-----------+
|    code    |   total   |
+------------+-----------+
| el_pr_25   |     45    |
| el_pr_11   |     33    |
| mob_tp_x93 |     23    |
| mob_tp_t55 |     33    |
| el_pr_x73  |     25    |
| mob_tp_25  |     22    |
+------------+-----------+

= electricity =
+-----+-------+
|  id | name  |
+-----+-------+
|  25 | test1 |
|  11 | test2 |
| x73 | test3 |
+-----+-------+

  == mobile ==
+-----+----------+
|  id |   name   |
+-----+----------+
| x93 | test 66  |
| t55 | test 222 |
| 25  | test 323 |
+-----+----------+

the main table i want to select is the inventory table, the inventory table is connected to the other table through code column, for electricity table there is a el_pr_ prefix followed by the id of the electricity table and for mobile table the prefix is mob_tp_ is the prefix, i want to select the inventory table with the name column from the electricity and mobile table, for example the result will be like this:

SELECT code,total, ... as name FROM inventory; 


         === inventory ===
+------------+-----------+----------+
|    code    |   total   |  name    |
+------------+-----------+----------+
| el_pr_25   |     45    | test1    |
| el_pr_11   |     33    | test2    |
| mob_tp_x93 |     23    | test 66  |
| mob_tp_t55 |     33    | test 22  |
| el_pr_x73  |     25    | test3    |
| mob_tp_25  |     22    | test 323 |
+------------+-----------+----------+

Answer 1

We can try the following join query:

SELECT
    i.code,
    i.total,
    COALESCE(e.name, m.name) AS name
FROM inventory i
LEFT JOIN electricity e
    ON i.code REGEXP CONCAT('el_.*_', e.id, '$')
LEFT JOIN mobile m
    ON i.code REGEXP CONCAT('mob_.*', m.id, '$');

Demo

The above query uses a COALESCE trick to choose the correct name for each item, which assumes that a given item would only match to either the electricity or mobile table.

But, your database design is not ideal. It would be much better to just have a single table containing metadata for both mobile and electrical (and other) items. In addtion, your tables should have proper join columns which don't require complex substring or regex operations to match. I suggest the following:

inventory
+----+------------+-----------+
| id |    code    |   total   |
+----+------------+-----------+
| 1  | el_pr_25   |     45    |
| 2  | el_pr_11   |     33    |
| 3  | el_pr_x73  |     25    |
| 4  | mob_tp_x93 |     23    |
| 5  | mob_tp_t55 |     33    |
| 6  | mob_tp_25  |     22    |
+----+------------+-----------+

items
+--------------+----------+-------------+
| inventory_id | name     | type        |
+--------------+----------+-------------+
| 1            | test1    | electricity |
| 2            | test2    | electricity |
| 3            | test3    | electricity |
| 4            | test 66  | mobile      |
| 5            | test 222 | mobile      |
| 6            | test 323 | mobile      |
+--------------+----------+-------------+

Answer 2

you may use inner join with union [all] as :

select i.*, e.name from inventory i 
                   inner join electricity e on ( i.code = concat('el_pr_',e.id) ) union all
select i.*, m.name from inventory i 
                   inner join mobile m on ( i.code = concat('mob_tp_',m.id) );

Rextester Demo

Answer 3

I would suggest changing your data model into separate columns, and with a unique constraint formed by using them both.

|code  |   total   |type
+------+-----------+
| 25   |     45    |el_pr_
| 11   |     33    |el_pr_
| x93  |     23    |mob_tp_
| t55  |     33    |mob_tp_
| x73  |     25    |el_pr_
| 25   |     22    |mob_tp_

and combining the source of names into a single table

|  id | name  |type
+-----+-------+
|  25 | test1 |el_pr_
|  11 | test2 |el_pr_
| x73 | test3 |el_pr_
| x93 | test 66  |mob_tp_
| t55 | test 222 |mob_tp_
| 25  | test 323 |mob_tp_

then

select *
from inventory i
join tab_names n on i.type = n.type and i.code = n.id

This is just a rough outline!