CODEWITHSUNDEEP
sqlsql-server
Pinkab
Pinkab

Reputation: 21

Using a third table in simple left outer join

I have an simple sql that is fine but I need data from another table and getting error below: the trick is that each people_id might have more than one program_name (we are an agency with many programs) I want to show Full Name, the alt_id info and programs they are in. I want to show programs even if they do not have an alt_id.

select b.*, c.program_name, a.full_name 
from all_clients_view a 
left outer join alt_id_view b
on a.people_id = b.people_id

*** till here it is fine. This part is giving error:

Where a.people_id IN (select program_name
                      from program_enrollment_view c
                      where a.people_id = c.people_id)

Error:

The multi-part identifier "c.program_name" could not be bound.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I suspect that you simply want exists:

where exists (select 1
              from program_enrollment_view pev
              where a.people_id = pev.people_id
             )

Comparing a "people_id" to a "program_name" doesn't make sense to me.

If you want to return the program name, you want an additional join:

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;

Notes:

  • This uses table aliases that make sense, rather than a, b, and c. Highly recommended!
  • You want to left join the view program_enrollment_view, if you want rows with no matches.
  • Because you are selecting * from the second table, I am guessing that an inner join is more appropriate.

Upvotes: 1

Related Questions