Home Mysql use another table to translate value
Reply: 2

Mysql use another table to translate value

Benjamin W
1#
Benjamin W Published in 2018-01-13 03:48:59Z

table user

+-----+-------+----------+------------+
| id  |  name |  gender  |   computer |
+-----+-------+----------+------------+
| 1   |  ben  |     1    |       3    |
+-----+-------+----------+------------+

table translate

+-----+--------+
| id  |  trans |
+-----+--------+
| 1   |   boy  |
| 2   |   girl |
| 3   |   pc   |
| 4   |   mac  |
+-----+--------+

final result

+-----+-------+----------+----------+
| id  |  name |  gender  | computer |
+-----+-------+----------+----------+
|  1  |  ben  |  boy     | pc       |
+-----+-------+----------+----------+

I have 2 tables, one is user's data, the other is translate.

I want to use translate table to translate user's gender & computer's values anyone know how to achieve this?

Wanderer
2#
Wanderer Reply to 2018-01-13 04:11:08Z

Use JOIN.

Query

 select 
   t1.`id`,
   t1.`name`,
   t2.`trans` as `gender`,
   t3.`trans` as `computer`
from `user` t1
join `translate` t2
on t1.`gender` = t2.`id`
join `translate` t3
on t1.`computer` = t3.`id`;
AjahnCharles
3#
AjahnCharles Reply to 2018-01-13 04:23:52Z

The way you've presented this, it doesn't appear to be a translation problem (in the sense of language), but rather a standard database partition problem. A more standard solution would be to define your entities each in their own tables:

user

+-----+--------+------------+--------------+
| id  |  name  |  genderId  |  computerId  |
+-----+--------+------------+--------------+
| 1   |  ben   |     1      |       1      |
+-----+--------+------------+--------------+

gender

+-----+--------+
| id  |  name  |
+-----+--------+
| 1   |  boy   |
+-----+--------+
| 2   |  girl  |
+-----+--------+

computer

+-----+--------+
| id  |  name  |
+-----+--------+
| 1   |  pc    |
+-----+--------+
| 2   |  mac   |
+-----+--------+

...and then your query reads:

SELECT * 
FROM user
INNER JOIN gender ON user.genderId = gender.id
INNER JOIN computer ON user.computerId = computer.id
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312832 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO