Hi all, I have the following query which seeks to return all rows from the first table regardless of whether or not there is a matching record in the second table. I know for a fact it's missing some rows. There ought to be some rows where there is no entry for a particular 'assignno' in the secon table, but they are not appearing. Your help is greatly appreciated. Thanks so much.
select a.assignno, a.itemno, a.serno ,a.oan, a.description, a.brand, a.modelno, mostrecent = max(c.tested)
from inventory a
left join armsqualify c on a.serno = c.SERNO and a.assignno = c.perno
where a.CATEGORYNO = 'ECSO09PRC00000049'
and a.ASSIGNNO like 'ecso%per%'
Having max(c.tested) < dateadd(day, -365, '5/13/2020')