join 3 sql tables and make nested query

Question

So I have 3 tables called comments, users and posts. I want to get

1. the "score" from comments

2. the "user reputation" for users by doing the left join between comments and users with c.user_id = u.id

3. get the "tags" from posts table by doing a left join between comments and posts on c.post_id = p.id.

   BUT there is a trick here the tags should be based on the type of the posts (p.post_type_id).

   So if the id = 1 then that means we have a "question" as a post and simply retrieve tag

   Else if the id = 2 then that means we have an answer and to get the tag we have to look at its parent_id from posts table.

I tried to use WHERE, CASE, nested IF, and nested SELECT but all threw syntax errors

Finally, I tried to do the following but I got an empty result

SELECT c.score, 
    COALESCE (u.reputation) reputation,
    COALESCE (p.tags) tags
FROM comments c 
    LEFT JOIN users u 
        ON c.user_id = u.id
    LEFT JOIN posts p 
        ON (c.post_id = p.id AND p.post_type_id = 1) OR (c.post_id = p.id AND p.post_type_id = 2 )
             WHERE (p.id = p.parent_id)

So how can I have the tags based on the two types ?

Answer 1

Just quick guess:

http://sqlfiddle.com/#!9/a1cc3/1

SELECT c.score, 
    u.reputation reputation,
    IF(p.post_type_id=1,p.tags,
                 IF(p.post_type_id=2,parents.tags,'UNKNOWN POST TYPE')
       ) tags
FROM comments c 
    LEFT JOIN users u 
        ON c.user_id = u.id
    LEFT JOIN posts p 
        ON c.post_id = p.id  
    LEFT JOIN posts parents 
       ON parents.id = p.parent_id

UPDATE Here is Postgres variant:

http://sqlfiddle.com/#!15/a1cc3/2

SELECT c.score, 
    u.reputation,
    CASE p.post_type_id 
         WHEN 1 THEN p.tags
         WHEN 2 THEN parents.tags
         ELSE 'UNKNOWN POST TYPE'
    END tags
FROM comments c 
LEFT JOIN users u 
   ON c.user_id = u.id
LEFT JOIN posts p 
   ON c.post_id = p.id  
LEFT JOIN posts parents 
   ON parents.id = p.parent_id