加入3个sql表并进行嵌套查询

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

所以我有3个表,称为评论,用户和帖子。我想得到

the "score" from comments

来自评论的“得分”

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

通过使用c.user_id = u.id在注释和用户之间进行左联接来为用户提供“用户信誉”

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

通过在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).

但是这里有一个技巧,标签应该基于帖子的类型(p.post_type_id)。

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

因此,如果id = 1那么这意味着我们将“问题”作为帖子并简单地检索标签

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.

否则,如果id = 2那么这意味着我们有一个答案并且获取标签我们必须从posts表中查看其parent_id。

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

我尝试使用WHERE,CASE,嵌套IF和嵌套SELECT,但都抛出了语法错误

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 ?

那么我如何根据这两种类型获得标签呢?

1 个解决方案

#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:

更新这里是Postgres变种:

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

#1