Forum OpenACS Q&A: Response to sql difficulties

Collapse
Posted by Dan Wickstrom on
Usually posting a simplified test case with table definitions makes these sorts of questions easier to figure out. I'm guessing from what you've posted so far that you want to have the comments summarized by comment_type and a count of how many times each question type has been viewed along with some type of weighting. If my assumptions are correct, then something like the following would work:

select count(gc.comment_id) as n_comments, 
       gc.comment_type,
       coalesce((select avg(wert) 
          from general_bewertung b
         where exists (select 1
                         from general_comments 
                        where comment_type = gc.comment_type
                          and b.comment_id = comment_id
                          and user_id = $user_id)),0) as durchschnitt_bewertung,
       (select count(gelesen_id) 
          from general_gelesen g
         where exists (select 1
                         from general_comments 
                        where comment_type = gc.comment_type
                          and g.on_what_id = comment_id
                          and user_id = $user_id)) as x_gelesen
       from general_comments gc
      where gc.user_id = $user_id
   group by gc.comment_type;