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.
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.
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.
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.
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=?
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=?
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=?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With