如何通过连接从两个表中获取值？

I have two tables table1 and table2:

我有两个表table1和table2:

table1:

id (primary key)
name
email
category1 (foreign key to table2.id)
category2 (foreign key to table2.id)
category3 (foreign key to table2.id)

table2: (categories)

id (primary key)
name

I want to write a select query to get the category names instead of the categoryId.

我想编写一个选择查询来获取类别名称而不是categoryId。

4 个解决方案

#1

You need 3 joins with the same table:

您需要使用同一个表进行3次连接:

SELECT t.id, t.name, t.email, c1.cat_name cn1, c2.cat_name cn2, c3.cat_name cn3 
FROM test t LEFT JOIN cat c1 ON (cat1=c1.id)
            LEFT JOIN cat c2 ON (cat2=c2.id)
            LEFT JOIN cat c3 ON (cat3=c3.id)

Tested with Mysql: DEMO

用Mysql测试:DEMO

#2

Confusing question..

Assuming that the table 2 contains the CategoryName and that the table 1 contains the category id for category 1, 2 and 3, you can proceed as;

假设表2包含CategoryName并且表1包含类别1,2和3的类别id,则可以继续;

SELECT t1.id, t2.CategoryName, t3.CategoryName, t4.CategoryName
FROM table1 t1
JOIN table2 t2
ON t1.category1Id = t2.id
JOIN table2 t3
ON t1.category1Id = t3.id
JOIN table2 t4
ON t1.category1Id = t4.id;

#3

You will have to join the category table once for every category (and use aliases to differentiate them):

您必须为每个类别加入一次类别表(并使用别名来区分它们):

select 
    t1.id, 
    t1.name, 
    t1.email, 
    t2_cat1.name,
    t2_cat2.name,
    t2_cat3.name
from 
    table1 t1 
join 
    table2 as t2_cat1 on t1.category1 = t2_cat1.id
join 
    table2 as t2_cat2 on t1.category2 = t2_cat2.id
join 
    table2 as t2_cat3 on t1.category3 = t2_cat3.id

If any of the table1.category* columns can be null you might want to use a left join instead to make sure data is returned for the other columns.

如果任何table1.category *列可以为null,则可能需要使用左连接来确保为其他列返回数据。

#4

SELECT T1.name,
       T1.email,
       C1.name AS CategoryName1,
       C2.name AS CategoryName2,
       C3.name AS CategoryName3
FROM table2 T1 
INNER JOIN table2 C1 
ON T1.category1 = C1.id
INNER JOIN table2 C2 
ON T1.category2 = C2.id
INNER JOIN table2 C3 
ON T1.category3 = C3.id

#1