Mixing
MySQL make an alias column from 2 different table
Clash Royale CLAN TAG#URR8PPP
up vote
7
down vote
favorite
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 |
+------------+-----------+----------+
mysql sql if-statement select
asked 1 hour ago
blue
1626
add a comment |Â
up vote
7
down vote
favorite
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 |
+------------+-----------+----------+
mysql sql if-statement select
asked 1 hour ago
blue
1626
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago
add a comment |Â
up vote
7
down vote
favorite
up vote
7
down vote
favorite
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 |
+------------+-----------+----------+
mysql sql if-statement select
asked 1 hour ago
blue
1626
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 |
+------------+-----------+----------+
mysql sql if-statement select
mysql sql if-statement select
asked 1 hour ago
blue
1626
asked 1 hour ago
blue
1626
edited 1 hour ago
asked 1 hour ago
blue
1626
asked 1 hour ago
blue
1626
asked 1 hour ago
blue
1626
1626
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago
add a comment |Â
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago
add a comment |Â
3 Answers
3
active
oldest
votes
up vote
4
down vote
accepted
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 |
+--------------+----------+-------------+
answered 1 hour ago
Tim Biegeleisen
202k1377126
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
add a comment |Â
up vote
1
down vote
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!
answered 56 mins ago
Used_By_Already
20.4k21736
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
add a comment |Â
up vote
0
down vote
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
answered 58 mins ago
Barbaros Özhan
9,79671430
add a comment |Â
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
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 |
+--------------+----------+-------------+
answered 1 hour ago
Tim Biegeleisen
202k1377126
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
add a comment |Â
up vote
4
down vote
accepted
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 |
+--------------+----------+-------------+
answered 1 hour ago
Tim Biegeleisen
202k1377126
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
add a comment |Â
up vote
4
down vote
accepted
up vote
4
down vote
accepted
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 |
+--------------+----------+-------------+
answered 1 hour ago
Tim Biegeleisen
202k1377126
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 |
+--------------+----------+-------------+
answered 1 hour ago
Tim Biegeleisen
202k1377126
edited 49 mins ago
answered 1 hour ago
Tim Biegeleisen
202k1377126
answered 1 hour ago
Tim Biegeleisen
202k1377126
answered 1 hour ago
Tim Biegeleisen
202k1377126
202k1377126
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
add a comment |Â
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
1
1
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
and you are fine with this as a data model?
– Used_By_Already
1 hour ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@Used_By_Already Yes, the database design is not ideal, and I'll add a comment about that.
– Tim Biegeleisen
57 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@TimBiegeleisen yes i know, i just receive the database from my higher up
– blue
51 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
@blue For your own information, as a suggestion, I gave a suggestion for how you might better structure your table data. Just use numbers for the inventory keys, and don't create tables all over the place if you could just have a single logical table which does the same thing.
– Tim Biegeleisen
48 mins ago
add a comment |Â
up vote
1
down vote
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!
answered 56 mins ago
Used_By_Already
20.4k21736
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
add a comment |Â
up vote
1
down vote
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!
answered 56 mins ago
Used_By_Already
20.4k21736
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
add a comment |Â
up vote
1
down vote
up vote
1
down vote
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!
answered 56 mins ago
Used_By_Already
20.4k21736
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!
answered 56 mins ago
Used_By_Already
20.4k21736
answered 56 mins ago
Used_By_Already
20.4k21736
answered 56 mins ago
Used_By_Already
20.4k21736
answered 56 mins ago
Used_By_Already
20.4k21736
20.4k21736
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
add a comment |Â
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
I don't know why this was downvoted +1. Not a direct answer, but it's a good suggestion in any case.
– Tim Biegeleisen
47 mins ago
add a comment |Â
up vote
0
down vote
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
answered 58 mins ago
Barbaros Özhan
9,79671430
add a comment |Â
up vote
0
down vote
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
answered 58 mins ago
Barbaros Özhan
9,79671430
add a comment |Â
up vote
0
down vote
up vote
0
down vote
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
answered 58 mins ago
Barbaros Özhan
9,79671430
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
answered 58 mins ago
Barbaros Özhan
9,79671430
edited 39 mins ago
answered 58 mins ago
Barbaros Özhan
9,79671430
answered 58 mins ago
Barbaros Özhan
9,79671430
answered 58 mins ago
Barbaros Özhan
9,79671430
9,79671430
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52961367%2fmysql-make-an-alias-column-from-2-different-table%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
You are expected to try a query of your own, then if having difficulty with it, ask us to assist. What query have you attempted to use? (Please add it into the question, don't use a comment for this).
– Used_By_Already
1 hour ago