Left Join – attempting to identify instances where child records do not exist, or if they do exist, populate unique records meeting specific criteria

I am attempting to search an SQL database for instances where child records do not exist, or if they do exist, they are a specific type (e.g. Historical) and no other types exist (e.g. Current):

SELECT distinct parent.id FROM parenttable 
Left Join childtable On childtable.primarykey = parenttable.primarykey 
Where childtable.id is null

This populates all of my parent records that do not have any child records without any issues. However, I also want the query to populate instances where the only child records that exist are historical (e.g. childtable.type = ‘Historical’). I have not been able to do this thus far.

Answer

You can just use not exists:

select p.id 
from parenttable p
where not exists (
    select 1
    from childtable c
    where c.primarykey = p.primarykey and c.type <> 'Historical'
)

This phrases as: get all records from the parent table that have no child whose type is other than “Historical” – parents without a child do satisfy this condition too.