提取一行在一个表中的另一个表链接到的数据多行 [英] Extract linked data from one row in one table to multiple rows in another table

问题描述

我创建一个游戏所遇到的需要从基于单个行所列的ID值的表（表1）选择名称。

行示例

的RowID 1单元UNIT2 UNIT3 UNIT4

和说，第1行填充为单位的数据作为

wfmatch的RowID 1单元UNIT2 UNIT3 UNIT4
----- ----- ----- ----- -----
1 1 2 3 4

然后在我的第二个表（表2 ）我有实际的名称

wfunitsUnitID名称
------ ----------
1与firstItem
2 Seconditem
3 Thirditem
4 Fourthitem

在一个查询或尽可能接近，我想能够获得的名称从第一个表给像这样的结果中列出的ID的：

这是因为在底部code取得的最终结果。 （+4更多的名字）的RowID 1单元UNIT2 UNIT3 UNIT4
----- --------- ---------- --------- ----------
1 Seconditem与firstItem Thirditem Fourthitem

我有一个去与JOIN声明，但我迄今一直缺乏关于如何正确地使用它们的知识，我觉得这可能是得到结果的方式，我只是不明白。

编辑：
试图code

  SELECT * FROM wfmatch为T1 INNER JOIN wfunits为T2 ON t1.crunit1 = t2.id WHERE t1.mid = 1
 

结果：阴性，只有一个名字提供

工作最终结果是：

  SELECT
m.mid，u1.name AS 1单元，u2.name AS UNIT2，u3.name AS UNIT3，u4.name AS UNIT4，
u5.name AS页免费，u6.name AS单词数，u7.name AS单词数，u8.name AS Unit8
从wfmatch为m
INNER JOIN wfunits AS U1 ON m.crunit1 = u1.id
INNER JOIN wfunits AS U2 ON m.crunit2 = u2.id
INNER JOIN wfunits AS U3 ON m.crunit3 = u3.id
INNER JOIN wfunits AS U4 ON m.crunit4 = u4.id
INNER JOIN wfunits AS U5 ON m.counit1 = u5.id
INNER JOIN wfunits AS U6 ON m.counit2 = u6.id
INNER JOIN wfunits AS U7 ON m.counit3 = u7.id
INNER JOIN wfunits AS U8 ON m.counit4 = u8.id
WHERE中旬= 1
 

解决方案

您可能会需要四个连接，每列一个，像这样的：

SELECT
  m.RefID，
  u1.Name AS 1单元，
  u2.Name AS UNIT2，
  u3.Name AS UNIT3，
  u4.Name AS UNIT4
FROM表1为m
  INNER JOIN表2 AS U1 ON m.Unit1 = u1.UnitID
  INNER JOIN表2 AS U2 ON m.Unit2 = u2.UnitID
  INNER JOIN表2 AS U3 ON m.Unit3 = u3.UnitID
  INNER JOIN表2 AS U4 ON m.Unit4 = u4.UnitID


还有一个选择，虽然我不知道这是否会更好：

SELECT
  m.RefID，
  MAX（CASE WHEN u.UnitID THEN m.Unit1 END u.Name）AS 1单元，
  MAX（CASE WHEN u.UnitID THEN m.Unit2 END u.Name）AS UNIT2，
  MAX（CASE WHEN u.UnitID THEN m.Unit3 END u.Name）AS UNIT3，
  MAX（CASE WHEN u.UnitID THEN m.Unit4 END u.Name）AS UNIT4
FROM表1为m
  INNER JOIN表2为u ON u.UnitID IN（m.Unit1，m.Unit2，m.Unit3，m.Unit4）
GROUP BY m.RefID


I am creating a game and have come across the need to select the name from a table (Table1) based on the ID value listed in a single row.

Example of the row

RowID Unit1 Unit2 Unit3 Unit4

And say that row 1 is populated with data for units as

wfmatch

RowID Unit1 Unit2 Unit3 Unit4
----- ----- ----- ----- -----
1     1     2     3     4

Then in my second table (Table2) I have the actual names

wfunits

UnitID Name
------ ----------
1      Firstitem
2      Seconditem
3      Thirditem
4      Fourthitem

In one query or as close as possible I would like to be able to obtain the names for the ID's listed from the first table giving a result like so:

this is the end result as achieved with code at the bottom. (+4 more names)

RowID Unit1     Unit2      Unit3     Unit4
----- --------- ---------- --------- ----------
1     Firstitem Seconditem Thirditem Fourthitem

I have had a go with JOIN statements but I have so far been lacking in the knowledge on how to use them properly and I feel this is probably the way to get the result, I just can't figure it out.

Edit: Attempted Code

SELECT * FROM wfmatch AS t1 INNER JOIN wfunits AS t2 ON t1.crunit1 = t2.id WHERE t1.mid = 1

result: negative, only one name provided.

Working end result:

SELECT
m.mid, u1.name AS Unit1, u2.name AS Unit2, u3.name AS Unit3, u4.name AS Unit4,
u5.name AS Unit5, u6.name AS Unit6, u7.name AS Unit7, u8.name AS Unit8
FROM wfmatch AS m  
INNER JOIN wfunits AS u1 ON m.crunit1=u1.id  
INNER JOIN wfunits AS u2 ON m.crunit2=u2.id 
INNER JOIN wfunits AS u3 ON m.crunit3=u3.id 
INNER JOIN wfunits AS u4 ON m.crunit4 =u4.id 
INNER JOIN wfunits AS u5 ON m.counit1=u5.id 
INNER JOIN wfunits AS u6 ON m.counit2=u6.id 
INNER JOIN wfunits AS u7 ON m.counit3=u7.id  
INNER JOIN wfunits AS u8 ON m.counit4 =u8.id 
WHERE mid=1

解决方案

You'll probably need four joins, one per column, like this:

SELECT
  m.RefID,
  u1.Name AS Unit1,
  u2.Name AS Unit2,
  u3.Name AS Unit3,
  u4.Name AS Unit4
FROM Table1 AS m
  INNER JOIN Table2 AS u1 ON m.Unit1 = u1.UnitID
  INNER JOIN Table2 AS u2 ON m.Unit2 = u2.UnitID
  INNER JOIN Table2 AS u3 ON m.Unit3 = u3.UnitID
  INNER JOIN Table2 AS u4 ON m.Unit4 = u4.UnitID

There's also an alternative, although I'm not sure if it would be better:

SELECT
  m.RefID,
  MAX(CASE u.UnitID WHEN m.Unit1 THEN u.Name END) AS Unit1,
  MAX(CASE u.UnitID WHEN m.Unit2 THEN u.Name END) AS Unit2,
  MAX(CASE u.UnitID WHEN m.Unit3 THEN u.Name END) AS Unit3,
  MAX(CASE u.UnitID WHEN m.Unit4 THEN u.Name END) AS Unit4
FROM Table1 AS m
  INNER JOIN Table2 AS u ON u.UnitID IN (m.Unit1, m.Unit2, m.Unit3, m.Unit4)
GROUP BY m.RefID

这篇关于提取一行在一个表中的另一个表链接到的数据多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！