Union on the same table - list of items with total number

Transact-SQL
1

Hi, I have the following 3 tables. I want to get a list of all properties with the number of tasks sorted by the oldest event whether there are tasks or events. I

property(property_id, property_addr,property_status),
comm_task_event(cte_id,,cte_status,cte_event(cte_event is FK of pe_id)
and property_event.(**pe_id,**pe_start,pe_property) (pe_property is FK of property_id)

My attempt

Select property_id,property_addr, '' as total,'' as pe_start from property  
where property_status  in(4,8) 
UNION
select property_id,property_addr,count(cte_id) as total,pe_start 
from comm_task_event join property_event on cte_event=pe_id
 join property  on pe_property=property_id
where property_status in(4,8)
AND cte_status=0
AND property_date is not null
group by property_id order by pe_start asc
2

It's rather confusing, but I think this is what you need:


select property_id, property_addr, cte.cte_count as total, pe_start 
from property p
left join property_event pe on pe.pe_property = p.property_id
left join (
    select cte_event, count(*) as cte_count
    from comm_task_event
    where cte_status = 0
    group by cte_event 
) as cte on cte.cte_event = pe.pe_id 
where p.property_status in(4,8)
AND p.property_date is not null
order by pe.pe_start asc
3

Thank you. It's only listing property that have events and multiple properties are coming. I want all properties wether there are tasks/events.

An example

Property ID, Address, Number to task, date
120, 120 street,5, 2022-05-19 (oldest start date first)
100, 100 street, 10, 2022-05-20
123, 123 street, 0, '' (this property have no events to tasks)