SQL 来自同一个表的多个 INNER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9370437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple INNER JOIN from the same table
提问by NCFUSN
I have a table of metals
我有一张金属表
MetalID integer
MetalName text
MetalCode text
Item table
项目表
ItemID integer
ItemName text
...
Metal1 int Ref.-> metals.metalID
Metal2 int Ref.-> metals.metalID
Metal3 int Ref.-> metals.metalID
I am trying to select three MetalCodes
我正在尝试选择三个 MetalCodes
SELECT m.MetalCode as 'Metal1', m.MetalCode as 'Metal2',m.MetalCode as 'Metal3'
FROM Item as k
INNER JOIN Metals AS m ON m.metalID=k.metal1
INNER JOIN Metals AS m ON m.metalID=k.metal2
INNER JOIN Metals AS m ON m.metalID=k.metal3
WHERE k.ItemID=?
Looks like I am doing it completely wrong. Please, help.
看起来我完全错了。请帮忙。
回答by Beatles1692
You should specify different aliases for your tables . you are calling all of them m.
您应该为您的表指定不同的别名。你在叫他们所有人。
SELECT m1.MetalCode as 'Metal1', m2.MetalCode as 'Metal2',m3.MetalCode as 'Metal3'
FROM Item as k
INNER JOIN Metals AS m1 ON m1.metalID=k.metal1
INNER JOIN Metals AS m2 ON m2.metalID=k.metal2
INNER JOIN Metals AS m3 ON m3.metalID=k.metal3
WHERE k.ItemID=?
回答by Dagg Nabbit
Well, not completelywrong. ;)
嗯，不完全错。;)
Wherever you have "INNER JOIN Metals AS m", mneeds to be something unique (not mevery time).
无论您在哪里拥有“INNER JOIN Metals AS m”，都m需要独特的东西（不是m每次都如此）。
Try something like this (not tested):
尝试这样的事情（未测试）：
SELECT m1.MetalCode as 'Metal1', m2.MetalCode as 'Metal2', m3.MetalCode as 'Metal3'
FROM Item as k
INNER JOIN Metals AS m1 ON m1.metalID=k.metal1
INNER JOIN Metals AS m2 ON m2.metalID=k.metal2
INNER JOIN Metals AS m3 ON m3.metalID=k.metal3
WHERE k.ItemID=?
回答by John Woo
try this:
尝试这个：
SELECT m.MetalCode as 'Metal1', n.MetalCode as 'Metal2'o.MetalCode as 'Metal3'
FROM Item as k INNER JOIN Metals AS m ON m.metalID=k.metal1
INNER JOIN Metals AS n ON n.metalID=k.metal2
INNER JOIN Metals AS o ON o.metalID=k.metal3
WHERE k.ItemID=?
回答by Vincent Vanmarsenille
SELECT m1.MetalCode as 'Metal1', m2.MetalCode as 'Metal2',m3.MetalCode as 'Metal3'
FROM Item as k
INNER JOIN Metals AS m1 ON m1.metalID=k.metal1
INNER JOIN Metals AS m2 ON m2.metalID=k.metal2
INNER JOIN Metals AS m3 ON m3.metalID=k.metal3
WHERE k.ItemID=?
or simpler but getting one metalcode per row
或更简单但每行获得一个金属代码
SELECT MetalCode
FROM Item
WHERE metalID = metal1 OR metalID = metal2 OR metalID = metal3
