How to do an outer join when there are conditions

1

HI Sorry if this is very basic but here I have a query that I am not getting all rows.
I am only getting the rows that also satisfy the where condition, of course.
But in this case I want also the rows on ac that are not in the ev table at all. The Where condition is checking according to where ev.category_code = 'PEOPLEPLANS' but I need a second select but that will give us 2 data sets - how would I code to have one output?
select
ev.full_name, ac.full_name, ev.actual_date, ev.end_date, CASE WHEN [Generic_Description] Like '% Review %' THEN 'Review' ELSE '' END AS 'Review Status',
CASE when ev.approved_date is null then 'Waiting for Approval' ELSE '' END AS 'Approval Status', ev.program_providing_service_name, ev.category_name,
ev.is_initial, ev.date_entered, ev.is_locked, ev.date_locked,
ev.approved_date, ev.generic_description,
ev.expiration_date, ev.dt_tm, ev.locked_staff_name, ev.approved_staff_name, ev.final_approval_date

from all_clients_view ac
full join event_expanded_view ev
on ac.people_id = ev.people_id

where category_code = 'PEOPLEPLANS'

2

I don't understand your question about 2 data sets, but to fix your query, you need a left join and the where clause should be in the on clause

select
ev.full_name, ac.full_name, ev.actual_date, ev.end_date, CASE WHEN [Generic_Description] Like '% Review %' THEN 'Review' ELSE '' END AS 'Review Status',
CASE when ev.approved_date is null then 'Waiting for Approval' ELSE '' END AS 'Approval Status', ev.program_providing_service_name, ev.category_name,
ev.is_initial, ev.date_entered, ev.is_locked, ev.date_locked,
ev.approved_date, ev.generic_description,
ev.expiration_date, ev.dt_tm, ev.locked_staff_name, ev.approved_staff_name, ev.final_approval_date

from all_clients_view ac
left join event_expanded_view ev
on ac.people_id = ev.people_id
and category_code = 'PEOPLEPLANS'