In your above example, testtaker is my id column?

Each f1, f2 ect has its own record in the table.

I tried this as well

select *

from testscores as ts1

where alpha_score_1 like 'f%'

and alpha_score_1 not like 'p%'

and im not getting the right people, its just not showing the record...

FYI the raw_score field is the one i want to pull and is directly related to the record type.

Peopel table-- primary key is people_code_id

The transcriptdetail table's primary keys are people_code_id, academic_year, academic_term, academic_session, event_id, event_sub_type, and section.

Id like to see a single row per student displaying EVERY...

This is what i got now:

select p.first_name

,p.last_name

,p.people_code_id

,case when td.event_id='sd 100' then (case transcript_seq when '002' then

(select final_grade from people as p

inner join transcriptdetail as td2

on p.people_code_id=td2.people_code_id

and td2.academic_year='2007'

and td2.academic_term='spring'

and...

I did this

and td3.transcript_seq='001'

and td3.final_grade<>'w')

else null end) else null end) as 'sd 100'

from people as p

inner join transcript_detail as td

on p.people_code_id=td.people_code_id

and receive the same error, can you...

This is closer, but still getting this error :

Msg 156, Level 15, State 1, Line 20

Incorrect syntax near the keyword 'as'

select p.first_name

,p.last_name

,p.people_code_id

,case when td.event_id='sd 100' then (case transcript_seq when...

I think were close, but theres a few things that dont make sense.

1. when '001' ? Shouldnt this be when transcript_seq='001'? If so, what join do i use?

2....

P is from the people table which primary key is people_code_id

The transcriptdetail table's primary keys are people_code_id, academic_year, academic_term, academic_session, event_id, event_sub_type, and section.

The reason i used the max...

Will this work? It appears to, but id like confirmation. Thanks!

select distinct p.first_name

,p.last_name

,adi.address_type

,adi.status

from people as p

left outer join addressschedule as adi

on p.people_code_id=adi.people_org_code_id

and adi.status='i'

inner join peopletype as pt

on pt.people_code_id=p.people_code_id

where p.deceased_flag='n'

and...

Clarification:

MY Case statement is ,max(case when td.event_id ='SD 099' then td.final_grade else null end) as 'SD 099'

,max(case when td.event_id ='SD 100' then td.final_grade else null end) as 'SD 100'

,max(case...

?? I dont understand what your saying here, can you clairfy?

Thanks for the help everyone. This was a tough one for me, but i learned alot!!

When using coalesce, and have something like

coalesce(t2.earned_credits, t1.earned_credits, 0)

IF, t2 or t1 do not return any rows, its going to use 0 for the aggregate function instead...