Which is correct, BUT, not everyone has data in the transcriptgpa table, for fall 2007 but, i still need to pull them based on their a.credits. if they dont have anything in the transcriptgpa table for fall 2007. How can i do this?

...left join transcriptgpa as t on p.people_code_id=t.people_code_id and t.record_type='O' and t.academic_term='Fall' and t.academic_year='2007'where a.academic_term='Spring' and a.academic_year='2008' and a.enroll_separation ='enrl' and a.academic_session='main' and (a.program ='ft' or a.program='pt')

Thanks!! It worked. Now i see the people who were previosuly missing. Ive never seen a join created like this, can you explain how exactly this is working? Im looking at it, and its just not jumping out yet. I really appreciate the help btw.

After further review it looks like that by adding those as a left outer join by using on p.people_code_id=t.people_code_id and t.record_type='O' and t.academic_term='Fall' and t.academic_year='2007'

its pulling IF they have a fall 2007 "o" record. If not, its just pulling their people record and using the data contained in the academic table for my case calulations, using the rest of the where clause? So my Case statement is seeing nothing in the transcriptgpa table, then using the rest of the where clause for definition. Am i close?

*edit*

I also notice now the people who are appearing are showing a class level of "null". Probably because they dont have any data in the transcriptgpa table, is their any way to use only the values in the acadmic table IF, they dont have data in the transcriptgpa table? Does this make sense?

One more thing i need to add...On their transcript gpa record, when it is pulling t.earned credits, their is also a trans seq number of either 001 or 002. Not everyone has a 002 record, BUT, if they do i need to use that record, if they dont have a trans seq of 002, then i need to use their 001 record. Is it possible to incoporate this into my code? This is what i have thus far, which is pulling everyone correctly, BUT, some people are appearing twice because they have both a trans seq 001 and 002 record, it should only be pulling their 002 record if they have it, if not, then 001.

select distinct p.first_name ,p.last_name ,'class level' = case when a.credits + isnull(t.earned_credits,0) between 1 and 32 and a.program ='ft'then 'F1' when a.credits + isnull(t.earned_credits, 0) between 33 and 61 and a.program ='ft' then 'F2'when a.credits + isnull(t.earned_credits, 0) >= 62 and a.program ='ft'then 'F3' when a.credits + isnull(t.earned_credits, 0) between 1 and 25 and a.program ='pt'then 'P1' when a.credits + isnull(t.earned_credits, 0) between 26 and 47 and a.program ='pt'then 'P2' when a.credits + isnull(t.earned_credits, 0) between 48 and 70 and a.program ='pt'then 'P3' when a.credits + isnull(t.earned_credits, 0) >= 71 and a.program ='pt'then 'P4' endfrom people as p inner join academic as a on p.people_code_id=a.people_code_id left outer join transcriptgpa as t on p.people_code_id=t.people_code_id and t.record_type='O' and t.academic_term='Fall'--records in the transcript gpa table and t.academic_year='2007' where a.academic_term='Spring' and a.academic_year='2008' and a.enroll_separation ='enrl' and a.academic_session='main' and (a.program ='ft' or a.program='pt')order by 'class level'