Using a third table in small left outer join

  • pk400i

    SSC-Addicted

    Points: 409

    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

     

  • drew.allen

    SSC Guru

    Points: 76644

    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

  • pk400i

    SSC-Addicted

    Points: 409

    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

     

     

     

     

    • This reply was modified 6 months, 2 weeks ago by  pk400i.
  • drew.allen

    SSC Guru

    Points: 76644

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply