Multiple INNER JOIN from the same table

Question

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

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.

You can't have multiple aliases with the same name. – rid Feb 21 '12 at 00:25 — rid, Feb 21 '12 at 00:25

Beatles1692 · Accepted Answer · 2019-04-06T08:26:24.883

47

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=?

edited Apr 06 '19 at 08:26

answered Feb 21 '12 at 00:26

Beatles1692

5,214
34
65

4

nice, we wrote exactly the same example code at exactly the same time. Must be twins. +1, in fact :) – Dagg Nabbit Feb 21 '12 at 00:29
Theres still something wrong. I am getting result -> Zn,Zn,Zn while there's different metals – NCFUSN Feb 21 '12 at 00:36
1

@Nathan Considering we wrote these answers in a matter of seconds without testing, that's extremely probable. These examples should get you headed in the right direction though. – Dagg Nabbit Feb 21 '12 at 00:39
2

maybe something is wrong with your data please check to see whether k.metail1,k.metal2,k.metal3 having the same value ? – Beatles1692 Feb 21 '12 at 00:41
Yes, that was the real answer-boom. 7 answers in a couple of seconds. – NCFUSN Feb 21 '12 at 00:42
4 - Zinc, 1-UNDEF. So, logically the result should be Zinc,UNDEF,UNDEF – NCFUSN Feb 21 '12 at 00:47
are you using m1.MetalCode,m2.MetalCode and m3.MetalCode in your select list ? – Beatles1692 Feb 21 '12 at 01:00

Dagg Nabbit · Answer 2 · 2012-02-21T00:48:37.413

Well, not completely wrong. ;)

Wherever you have "INNER JOIN Metals AS m", m needs to be something unique (not m every time).

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=?

score 4 · Answer 3 · answered Feb 21 '12 at 00:27

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=?

score 2 · Answer 4 · answered Feb 21 '12 at 00:28

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

Multiple INNER JOIN from the same table

4 Answers4

Linked