May 8, 2019 at 1:25 pm
HI I have 2 views and I need one more information and not sure how to do this. I thank you for all of your help so far.
select b.*, a.full_name
from all_clients_view a
left outer join alt_id_view b
on a.people_id = b.people_id
I need to add in the program_name from this view: I don't need people_id really but it would be the column to link on.
One point, there can be more than one program for each people_id. Those who did not match in the top one (did not have a Alt-ID), we still want to see all their programs. I am not sure how to structure this. I am doing where in (people_id a = People_id c) but it's not working.
(get the programs the people_id has:)
select people_id, program_name from program_enrollment_view c
May 8, 2019 at 1:55 pm
Simply saying It's not working isn't very descriptive. I assume that you mean that you're not getting the data that you expect. Since you haven't provided sample data and expected results, there isn't much that we can do to help you get those results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 8, 2019 at 2:01 pm
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
alt_id_view ai
on ac.people_id = ai.people_id left join
program_enrollment_view pev
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
May 8, 2019 at 2:44 pm
You're getting that error message, because the scope of the alias c is the sub-query, and you are trying to use that alias outside of its scope.
Also, it looks like you are choosing your aliases sequentially. This is a BAD IDEA. Aliases should be related to the object that they are aliasing. It makes it so much easier to remember which object corresponds to which alias if you make the aliases related to the object.
Try the following instead.
select ai.*, pe.program_name, ac.full_name
from all_clients_view ac
left outer join alt_id_view ai
on ac.people_id = ai.people_id
left outer join program_enrollment_view pe
ac.people_id = pe.people_id
There's also a problem with your original WHERE clause. It is likely to never be true, because you're comparing people_id to program_name.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply