complex joins in SQL Server 2008 -

complex joins in SQL Server 2008 -

i trying print rules applicants meet next id (626, 1985, 2511).

it prints applicants have 626 first rule, not print ones have 1985 , 2511 , no 626). idea?

select distinct ad1.appid, max(case when (apdr1.settingid= 626) apdr1.reviewruletext else ' ' end)as 'rule1', max(case when (apdr2.settingid= 1985)then apdr2.reviewruletext else ' ' end)as 'rule2', max(case when (apdr8.settingid= 2511) apdr8.reviewruletext else ' ' end)as 'rule8' appdetail ad1 left outer bring together apppartydetailrule apdr1 on apdr1.appid=ad1.appid , apdr1.appdetailid= ad1.appdetailid , apdr1.appdetailsavepointid= ad1.appdetailsavepointid , ad1.appdetailsavepointid=0 , apdr1.settingid= 626 left outer bring together apppartydetailrule apdr2on ad1.appid= apdr2.appid , ad1.appdetailid= apdr2.appdetailid , ad1.appdetailsavepointid= apdr2.appdetailsavepointid , ad1.appdetailsavepointid= 0 , apdr2.settingid= 1985 left outer bring together apppartydetailrule apdr8 on ad1.appid= apdr8.appid , ad1.appdetailid= apdr8.appdetailid , ad1.appdetailsavepointid= apdr8.appdetailsavepointid , ad1.appdetailsavepointid= 0 , apdr8.settingid= 2511 grouping ad1.appid,.... etc. (more fields)

table construction is: appid, appdetailid, appdetailsavepointid create primary key in ad1 table linked apdr few times using pk above , each time extract different value settingid.

desired output is: appid review rule text1, review rule text2, review rule text 8

review rule text1 represent 626, 2 1985 , 8 2511 , each represent rule

i think can simplify query to:

select ad1.appid, max(case when (apdr1.settingid = 626) apdr1.reviewruletext else '' end) 'rule1', max(case when (apdr1.settingid = 1985) apdr1.reviewruletext else '' end) 'rule2', max(case when (apdr1.settingid = 2511) apdr1.reviewruletext else '' end) 'rule8' appdetail ad1 left outer bring together apppartydetailrule apdr1 on apdr1.appid=ad1.appid , apdr1.appdetailid= ad1.appdetailid , apdr1.appdetailsavepointid= ad1.appdetailsavepointid , ad1.appdetailsavepointid=0 grouping ad1.appid apdr1.settingid in (626, 1985, 2511)

sql sql-server-2008-r2 left-join