MySQL - need to query from few columns

Question

Please help me to write correct query for a few tables. I need to replace all id here from another table api json

I am trying to make query like this

SELECT incident.`number`, `user`.first_name, (SELECT `user`.first_name  from ITSM.`user` JOIN incident on `user`.sys_id = incident.id_created_by) as createdby
from ITSM.incident
JOIN ITSM.`user` on incident.id_caller = `user`.sys_id
;*

but it doesn#t work, I got an error: Subquery returns more than 1 row

How can i make a right query?

This one doesn't work also, same error:

SELECT incident.`number`, (SELECT user.first_name from ITSM.`user`, ITSM.incident WHERE user.sys_id = incident.id_created_by) as createdby
from ITSM.incident
JOIN ITSM.`user` on incident.id_caller = user.sys_id*
;

and this is my DB id for user who created

Answer 1

You don't need a subquery. Just refer to the source table of each column in the select clause, here if you need 2 joins to the same table give these an alias and refer to the columns using that alias.

SELECT incident.`number`
    , caller.first_name as caller_name
    , creator.first_name AS createdby
FROM ITSM.incident
JOIN ITSM.`user` AS caller ON incident.id_caller = caller.sys_id
JOIN ITSM.`user` AS creator ON incident.id_created_by = creator.sys_id

Nb. I'm assuming your join logic is correct