How to merge those columns in mysql or should I use another join?

Question

So I'm tring to get a specifed information from database. I manage to do something like this but I don't know how instead of

weapon, armor, etc

merge it into one column named

items

SELECT char_name

     , obj_Id

     , items.item_id

     , weapon.name as weapon

     , armor.name as armor

     , etcitem.name as etc

  FROM characters 

  join items 

    on characters.obj_Id = items.owner_id

  LEFT 

  JOIN weapon 

    ON items.item_id = weapon.item_id

  LEFT 

  JOIN armor 

    ON items.item_id = armor.item_id

  LEFT 

  JOIN etcitem 

    ON items.item_id = etcitem.item_id;

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can use coalesce():

SELECT char_name, obj_Id, items.item_id,
       coalesce(weapon.name, armor.name, etcitem.name) as item_name
. . .

How to merge those columns in mysql or should I use another join?

Sign up