Mysql – Force number of entries

mysql-5.6PHP

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!

Best Answer

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 
...
Related Question