Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple INNER JOIN from the same table

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.

like image 855
NCFUSN Avatar asked Feb 21 '12 00:02

NCFUSN


People also ask

Can we apply inner join on same table?

SQL Server self join syntax It helps query hierarchical data or compare rows within the same table. A self join uses the inner join or left join clause. Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query.

Can you join the same table twice?

To self join the same table multiple times you can use the from parameter, as mentioned in the docs, to change the "join" name of the table. In this example, we join in person to order twice, with different names and different joining dimensions.

How do I join the same table with different conditions in SQL?

You can think of a self join as a join between two copies of the same table. For each record with a non-null value in spouse_id , we search for the value of customer_id that matches it. When we find a match, the columns firstname and lastname are added to the resulting table. Table aliases are required in a self join.


3 Answers

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=? 
like image 190
Beatles1692 Avatar answered Sep 25 '22 00:09

Beatles1692


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=?
like image 43
Dagg Nabbit Avatar answered Sep 23 '22 00:09

Dagg Nabbit


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=?
like image 45
John Woo Avatar answered Sep 26 '22 00:09

John Woo