Make an alias column from 2 different tables in MySQL

  • A+
Category:Languages

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 | +------------+-----------+----------+ 

 


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, '$'); 

Make an alias column from 2 different tables in MySQL

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      | +--------------+----------+-------------+ 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: