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.

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

View more solutions

Comments

[rid]

You can't have multiple aliases with the same name.

[YXD]

What are you trying to achieve?

[Dagg Nabbit]

nice, we wrote exactly the same example code at exactly the same time. Must be twins. +1, in fact :)

[NCFUSN]

Theres still something wrong. I am getting result -> Zn,Zn,Zn while there's different metals

[Dagg Nabbit]

@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.

[Beatles1692]

maybe something is wrong with your data please check to see whether k.metail1,k.metal2,k.metal3 having the same value ?

[NCFUSN]

Yes, that was the real answer-boom. 7 answers in a couple of seconds.

[NCFUSN]

4 - Zinc, 1-UNDEF. So, logically the result should be Zinc,UNDEF,UNDEF

[Beatles1692]

are you using m1.MetalCode,m2.MetalCode and m3.MetalCode in your select list ?