MySQL - 通过引用表从另一个表中选择列 [英] MySQL - Select column from another table via a reference table

问题描述

我有以下表格

table "users"
------------------------------
user_id    name    email
------------------------------
1          joe     [e-mail]
2          jane    [e-mail]
3          john    [e-mail]

table "code_to_user_ref"
---------------------------------
ref_id    user_id    code_id
---------------------------------
1         1          1
2         1          2
3         2          3
4         3          4

table "codes"
------------------
code_id    code    
------------------
1          xC1@3$
2          Cv@3$5
3          Vb#4%6
4          Bn%6&8

基本上，users 表是我所有注册会员所在的位置.codes 表只是外部使用的有效激活码列表.code_to_user_ref 表将每个用户映射到他们拥有的代码.

Basically, the users table is where all my registered members are. The codes table is just a list of valid activation codes for use externally. The code_to_user_ref table maps each user to the code/s that they own.

我想要做的是像这样回显表格:

What I want to do is echo a table like so:

------------------------------
Name    Email     Code/s owned
------------------------------
joe     joe@...   xC1@3$, Cv@3$5
jane    jane@...  Vb#4%6
john    john@...  Bn%6&8

我将如何写出这样的查询?

How would I write out a query for such?

推荐答案

尝试:

    SELECT a.name,a.email, GROUP_CONCAT(c.code)
      FROM users a 
      JOIN code_to_user_ref b ON a.user_id = b.user_id
      JOIN codes c ON b.code_id = c.code_id 
  GROUP BY a.name,a.email

结果是:

| NAME |        EMAIL |  CODE/S OWNED |
|------|--------------|---------------|
| jane | [e-mail] |        Vb#4%6 |
|  joe |  [e-mail] | Cv@3$5,xC1@3$ |
| john | [e-mail] |        Bn%6&8 |

这里是 SQLFiddle.

这篇关于MySQL - 通过引用表从另一个表中选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！