Sorry about that. I have the data ok but would like to know if it's possible to improve.
select ac.full_name, ai.*, pev.program_name
from all_clients_view ac left join
on ac.people_id = ai.people_id left join
on ac.people_id = pev.people_id
I would like to know if i can get all data on one row,
currently the above query will create a new row for each program the people_id has for example: this example the people_id has 2 alt_id and is in 2 programs.
Single, Jane CROWN ID 1030070000 8/21/2017 Union FIRST
Single, Jane CROWN ID 1030070000 8/21/2017 Parenting
Single, Jane SPIRIT ID 15905628 8/21/2017 Union FIRST
Single, Jane SPIRIT ID 15905628 8/21/2017 Parenting
So really we would want to see: I would do this in crystal reports but we have no way to use it directly in the Reporting system they give us in the EHR.
Single, Jane CROWN ID 1030070000 SPIRIT ID 15905628 Union FIRST Parenting