select additional data

[albertkao]

Given the following two SQL operations:

select prod_Id, P.proposal_id, P.institution_Name 
from proposals P 
group by P.proposal_ID 
having mark >= 50 
order by P.proposal_id desc 


select prod_id, P.proposal_id, PA.evaluator_ID, U.user_Given_Name, U.user_surname 
from users U, proposals P 
LEFT outer JOIN proposal_appraisal PA 
ON (P.proposal_Id = PA.proposal_id) 
where PA.evaluator_ID = U.user_id 
group by prod_id, PA.evaluator_ID 
order by PA.proposal_ID, U.user_ID desc

How to modify the following SQL operation so that prod_Id will be selected in addition to the existing columns being selected:

select PA.proposal_id, PA.evaluator_ID, PA.Primary_Evaluation_Status_ID, PA.Secondary_Evaluation_Status_ID, U.user_Given_Name, U.user_surname, 
from users U, proposal_appraisal PA 
where PA.evaluator_ID = U.user_id 
group by PA.proposal_id, PA.evaluator_ID 
order by PA.proposal_ID, U.user_ID desc

[smantscheff]

It's hard to tell without the table structure. How about:

select [B]P.prod_id, [/B]PA.proposal_id, PA.evaluator_ID, PA.Primary_Evaluation_Status_ID, PA.Secondary_Evaluation_Status_ID, U.user_Given_Name, U.user_surname, 
from users U, proposal_appraisal PA, [B]proposals P[/B]
where PA.evaluator_ID = U.user_id 
[B]and P.proposal_id=PA.proposal_id[/B]
group by PA.proposal_id, PA.evaluator_ID 
order by PA.proposal_ID, U.user_ID desc

[d5e5]

Could you have more than one prod_id per proposal-evaluator group? If so, which prod_id do you want... first, last, greatest, least? Or maybe you should group by prod_id in addition to PA.proposal_id and PA.evaluator_ID.