sql server - SQL:where column not equal return result else result equals blank value -

so coming across issue sql query (mssql) have been working on has multi-statement clause, , 1 of statements may or may not return value. if condition not met how can have return empty value , still return rest of results? using multiple ctes.

here select clause:

select cte_devinfo.serialnumber,     cte_devinfo.devicename,     cte_devinfo.deviceid,      dev_cte.concurrencies,     (cte_slots.locationindex +1) 'total media',     cte_changer.slotcount, cte_changer.totalmounterrors, cte_changer.totalmounts,     cte_mismatch.mismatchserialnumber cte_devinfo, dev_cte, cte_slots, cte_changer, cte_mismatch 

here clause:

where cte_devinfo.deviceid = dev_cte.deviceparentid  , cte_slots.locationid = dev_cte.deviceparentid , cte_changer.changerid = dev_cte.deviceparentid , cte_mismatch.locationid = dev_cte.deviceparentid  

i want add clause:

and cte_mismatch.mismatchserialnumber != cte_devinfo.serialnumber  

but condition may never occur, , if doesn't how can ignore condition , return ' ' rest of query run?

first, rewrite query using ansi joins:

select cte_devinfo.serialnumber,     cte_devinfo.devicename,     cte_devinfo.deviceid,      dev_cte.concurrencies,     (cte_slots.locationindex +1) 'total media',     cte_changer.slotcount, cte_changer.totalmounterrors, cte_changer.totalmounts,     cte_mismatch.mismatchserialnumber cte_devinfo inner join dev_cte on cte_devinfo.deviceid = dev_cte.deviceparentid left outer join cte_slots on cte_slots.locationid = dev_cte.deviceparentid left outer join cte_changer on cte_changer.changerid = dev_cte.deviceparentid left outer join cte_mismatch on cte_mismatch.locationid = dev_cte.deviceparentid  

i changed joins except first 1 outer, allow missing records slots, changers, , mismatches. dev_cte remains required, though, because tables joined record it.

now can add where clause this:

where cte_mismatch.mismatchserialnumber null or cte_mismatch.mismatchserialnumber != cte_devinfo.serialnumber 

this condition allows null in mismatchserialnumber, or missing cte_mismatch record.