1

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 |
  • Please tag your DBMS (version included) – McNets Mar 4 '18 at 18:46
0

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;
  • Perfect, the second query has missing ',' before i.status, on h2.name, Thanks McNets! – CorTheZ Mar 4 '18 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.