Issue with join and using same table column twice in selection

I’m having bit of issue with joins here: Tasking is to select Item code, Item name and Concat together that items prerequisites item code and name together.

I can get the select to work to show item code, item name correctly and the prerequisite code in the concat, but not the name of it. What’s causing the pre-requisite item name to remain wrong here? Query response I’m looking for should come as this:

item_code, item_name, prereq

A126     ,  Muffin , A1 Flour

But what i’m getting is this: (it’s supposed to be using the Prerequisite ingredient_code (correct in concat) to find the name corresponding to it from Item.Item_name, but it’s matching up as wrong name)

item_code, item_name, prereq

A126     ,  Muffin  , A1 Muffin

Code I’m using:

SELECT Item.item_code, Item.item_name, CONCAT(Prerequisite.ingredient_code, ' ', Item.item_name) AS "prereq"
FROM Item
JOIN Prerequisite ON (Item.item_code = Prerequisite.item_code)
WHERE Item.item_code ='A126'

Answer

You want a second join for the prerequisite item. Without sample data it is hard to be sure which columns line up, but I thin you want:

SELECT i.item_code, i.item_name,
       CONCAT(p.ingredient_code, ' ', ip.item_name) AS "prereq"
FROM Item i JOIN
     Prerequisite p
     ON i.item_code = p.item_code JOIN
     Item ip
     ON ip.item_code = p.ingredient_code  -- guess as to the name
WHERE i.item_code ='A126'