Join or Replace colum value based on query I have this query:

SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description 
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i.status = 0
WHERE h.name LIKE "ABC%" OR h.name LIKE "123%"
ORDER BY templateid DESC
LIMIT 1;


Result is:

h.name | i.name | i.delay | i.delay_flex | i.status | 43206 | i.params | i.description |


I need to replace value 43206 with this query:

SELECT name 
FROM hosts 
WHERE hostid = (SELECT hostid 
                FROM items 
                WHERE itemid = (SELECT '43206'));


The result is: Template XPTO

And expected result is:

    h.name | i.name | i.delay | i.delay_flex | i.status | Template XPTO | i.params | i.description |
You can use an scalar subquery in this way:

SELECT    h.name, 
          i.name, 
          i.delay, 
          i.delay_flex, 
          i.status, 
          (SELECT name 
           FROM hosts 
           WHERE hostid = (SELECT hostid 
                           FROM items 
                           WHERE itemid = i.templateid)), 
          i.params, 
          i.description 
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
WHERE     h.name LIKE "ABC%" 
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;


Or you can add both tables hosts and items to the JOIN section:

SELECT    h.name, 
          i.name, 
          i.delay, 
          i.delay_flex, 
          i.status, 
          h2.name, 
          i.params, 
          i.description 
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
LEFT JOIN items as it
ON        it.itemid = i.templateid
LEFT JOIN hosts as h2
ON        h2.hostid = it.hostid
WHERE     h.name LIKE "ABC%" 
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;