Left Join not working?

1

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%'
Group By
a.assignno
, a.itemno
, a.serno
, a.oan
, a.description
, a.brand
, a.modelno
Having max(c.tested) < dateadd(day, -365, '5/13/2020')

2

The having clause makes it an inner join. Change the join to include the logic you have.

left join armsqualify c on a.serno = c.SERNO and a.assignno = c.perno and c.tested < dateadd(day, -365, '5/13/2020')

1 Like
3

HAVING MAX(c.tested) IS NULL OR MAX(c.tested) < dateadd(day, -365, '20200513')

4

Thanks a lot Scott. I believe that is what I was needed. Appreciate this forum.