ok well then lets try to Use a LEFT JOIN to dim_vulnerability_reference — not a regular (inner) join and Remove dvr.source IN (‘CVE’, ‘NVD’) from the WHERE clause , or it undoes the outer join, then we can prioritize CVE/NVD sources if you want only one reference.
SELECT
da.host_name AS "Asset Name",
dvr.reference AS "CVE ID",
dv.nexpose_id AS "Vulnerability ID",
dv.title AS "Vulnerability Title"
FROM
dim_asset da
JOIN fact_asset fa ON da.asset_id = fa.asset_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
-- LEFT JOIN here to keep non-CVE vulns
LEFT JOIN dim_vulnerability_reference dvr
ON dv.vulnerability_id = dvr.vulnerability_id
AND dvr.source IN ('CVE', 'NVD')
LIMIT 50;
and then if you want something at least in the row/column when no CVE ID exists
COALESCE(dvr.reference, dv.nexpose_id) AS "Vulnerability Identifier"
Or separate it
CASE
WHEN dvr.reference IS NOT NULL THEN dvr.reference
ELSE NULL
END AS "CVE ID",
dv.nexpose_id AS "Vulnerability ID"