0

My title probably isn't the best, but essentially I have a table that stores a host, vulnerability finding reference, and status. I am trying to query on those, but where I don't have an entry I want to force it to show me a defaulted value for the status. Here's an example...

SELECT f.host_id, f.`status` FROM findings f WHERE f.finding_id = 1 AND f.host_id IN (1,2,3)

So if there is an entry for 1 & 2, but not 3, I will want an entry for 3, but with a defaulted status value of 'No Data'.

TIA

EDIT:
I am trying to avoid doing 3 queries because I may have dozens of hosts in that list and that would be a major performance hit!

  • So, you want 3 rows even if this query would return only 2 (or 1 or none at all)? – ypercubeᵀᴹ Mar 5 '14 at 16:13
  • Yes, that is exactly what I want – Slinky33 Mar 5 '14 at 16:16
0

You can do it with a (derived) table that holds these 3 values and then LEFT joining it to the findings:

SELECT
    d.host_id, 
    CASE WHEN f.host_id IS NOT NULL THEN f.status ELSE 'No Data' END AS status 
FROM 
    ( SELECT 1 AS host_id UNION ALL
      SELECT 2 UNION ALL
      SELECT 3
    ) AS d
  LEFT JOIN 
    findings AS f 
      ON  f.finding_id = 1 
      AND f.host_id = d.host_id ; 

If the findings.status column is not nullable, you can replace the CASE ... END with the simpler:

    COALESCE(f.status, 'No data') AS status

If you have a hosts table (and a large number of IDs to check), the derived table can also be simplified:

...
FROM 
    ( SELECT host_id 
      FROM hosts
      WHERE host_id IN (1,2,3)
    ) AS d
  LEFT JOIN 
...

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.