3

I want to modify the query below so that the answers to the questions are also included. The full code is here. I would also like to print/report the results in order, so question, answer1, answer2 ... Basically I'd like the results to look a lot like what one sees on the original website. So there's two tasks.

One of my attempts to get the answers was to add the line commented out, --where (p.PostTypeId = 1 or p.PostTypeId = 2) but this does not work, giving Incorrect syntax near the keyword 'inner'. This looks like pretty innocent sql so I'm not sure what the problem is. With this line commented out, the code returns just the original question (which I guess means PostTypeID defaults to 1).

How should I include the answers and order things so that the answers are with the corresponding question?

begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_posts 
               select @url
               , q.id
               , q.title
               , p.id
               , p.creationdate
               , p.score
               , p.body
               from posts p
               --where (p.PostTypeId = 1 or p.PostTypeId = 2)
               inner join votes v on v.PostId = p.id 
                 and v.VoteTypeId = 5
               --inner join posts q on q.id = coalesce(p.parentid, p.id)
               inner join posts q on q.id = p.id  -- same as above apparently
               inner join users u on u.accountid = '+ cast(@accountid as nvarchar) + '
                 and v.UserId = u.id;'
      exec (@sql)
   fetch next from db_c into @db_c_name
end;
1

WHERE clauses need to go at the end, after all JOINs. That's why it's giving a syntax error. But I don't think you need it; you can only favorite questions anyway. I think

--inner join posts q on q.id = coalesce(p.parentid, p.id)

shouldn't be commented out; for answers, parentid is the post ID of the question, and for questions it's NULL (hence the COALESCE). If you limit yourself to q.id = p.id, you'll only get questions precisely because of the join on the Votes table.

           insert into #all_posts 
           select @url
           , q.id
           , q.title
           , p.id
           , p.creationdate
           , p.score
           , p.body
           from posts p
           inner join posts q on q.id = coalesce(p.parentid, p.id)
           inner join votes v on v.postid = q.id
                             and v.votetypeid = 5
           inner join users u on v.userid = u.id
             and u.accountid = ' + cast(@accountid as nvarchar) + '
           order by q.id, p.creationdate;

The last line ensures the posts are grouped by question.

| improve this answer | |
  • The script retrieves all the questions I have favorited (I don't think I have favorited my own much or at all). I had tried some variations in which the WHERE came last but I had different errors there. – Bryan Hanson Feb 3 at 16:03
  • Ah, you're right. I misinterpreted your request, I'll try to fix it – Glorfindel Feb 3 at 16:05
  • Could you look at my modifications? With your suggestion I'm getting Incorrect syntax near ','), '. Must declare the scalar variable "@db_r_site". Must declare... errors, I think I missed some character somewhere. – Bryan Hanson Feb 3 at 17:34
  • Ah, found it, closing tick was missing. Testing now. Looks like it works. Thank you so much! – Bryan Hanson Feb 3 at 17:38
  • Looks like the answers are not with the questions but I will investigate further. – Bryan Hanson Feb 3 at 17:55
  • Several variations on order by seem not to work quite right. Could the section process results be overriding the sort? – Bryan Hanson Feb 3 at 18:07
  • That's worth investigating, even though it is a straight select. – Glorfindel Feb 3 at 18:11
  • 1
    @BryanHanson You have to do the order by on that final select indeed, it is of no use to do that in the data collection step, except for warming up the planet. order by id, creationdate might work. Without an order by clause Sql Server will spool rows in any order, preferable from the memory cache first before fetching from disk. With newer sql server versions it becomes less and less predictable what the order will be for a projection without an explicit order by clause. – rene Feb 3 at 21:53
  • @rene Thanks so much for the background and suggestion, which works perfectly. – Bryan Hanson Feb 3 at 22:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .