I want to return status unable to do so when using groupBy

Question

select a.NAME
from tableA a 
left outer join tableB b on a.id = b.xxx_id
where a.is_deleted = false
group by a.Name having count(b.id) = 0;

Using the above code, returns the list of names.

Results: Row NAME

     1      Name1 
     2.     Name2
     3.     Name3

I am using the below code to return name status.

select a.NAME, a.name_status
from tableA a 
left outer join tableB b on a.id = b.xxx_id
where a.is_deleted = false
group by a.Name having count(b.id) = 0;

However, when I add a.name_status it is giving me an error saying'a.name_status in select clause is neither an aggregate nor in the group by clause.

Desired output: Row NAME. Name_status

     1      Name1      Pending
     2.     Name2      Expired
     3.     Name3.     Active

Answer 1

Given your current query I will guess that would you want could be done using not exists, although to be sure actual sample data would help clarify.

select a.NAME, a.name_status
from tableA a 
where a.is_deleted = false
and not exists (
  select * from tableB b
    where b.xxx_id = a.id
);