How to use the columns of the subquery

1

I would like to show data from the subquery. I would like to show c.approved_by_description and other columns but in main part it will not allow the subquery columns:
Select a.Full_name, b.program_start_date, b.program_end_date from all_clients_view a
inner join program_enrollment_expanded_view b
on a.people_id = b.people_id

where a.discharge_dt is not null and b.program_name = 'CFTSS'
and a.people_id not in (select c.people_id from service_plan_goals_objectives_methods_view c where c.approved_by_description is not null) order by full_name

2

provide sample data.

3

Trying to SELECT something from alias "c" doesn't make sense in that query, since you have a WHERE condition that the people_id NOT be in c -- what then could there be to list from c, since no matching can exist??

If you want list all rows from a, and only the (first) matching row(s) from table c, if one exists, then do something like this:

Select a.Full_name, b.program_start_date, b.program_end_date,
    c.approved_by_description /*, c...<other_column_name>, ...*/
from all_clients_view a
inner join program_enrollment_expanded_view b
on a.people_id = b.people_id
outer apply (
    select /*top (1)*/ *  --uncomment the "top (1)" if you want only 1 row per people_id in the query result
    from service_plan_goals_objectives_methods_view c
    where c.people_id = a.people_id and c.approved_by_description is not null
) as c
where a.discharge_dt is not null and b.program_name = 'CFTSS'
order by full_name
2 Likes