Joining a table after a self join on the same table-CodePudding

I have a total of two tables, one that is called market_orders, and the other called item_names. Currently in the below code I am only using market_orders table and doing an inner join back to the same table.

The output of the way this code is now is perfect however a.item_id is in "Non readable english". This is why i created the table item_names that has all the same item_ids as the table being used below, but then it also has a column named item_names that contains "Readable english". I am having trouble joining the new table item_names onto what I have already.

select a.item_id,
case when b.price > a.price then (b.price - a.price) Else 'false' END as Sellable
from market_orders a
inner join market_orders b on a.item_id = b.item_id



where a.location = 4002
and b.location = 3003
and a.enchantment_level = 3
and b.enchantment_level = 3
and a.quality_level = 1
and b.quality_level = 1
group by item_id order by  sellable

CodePudding user response：

Add an inner join with the item_names table, and add the name to the select list.

select a.item_id, n.item_names,
    case 
        when b.price > a.price then (b.price - a.price) 
        Else 'false' 
    END as Sellable
from market_orders a
inner join market_orders b on a.item_id = b.item_id
inner join item_names n on a.item_id = n.item_id
where a.location = 4002
    and b.location = 3003
    and a.enchantment_level = 3
    and b.enchantment_level = 3
    and a.quality_level = 1
    and b.quality_level = 1
group by a.item_id order by  sellable

DEMO