[mysql]未知的列 'parent.username' 在字段列表 2 表上做左联接

标签: sql MySQL
发布时间: 2015/6/11 23:10:05
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我建立一个嵌套的注释功能。我有 submissions_comments 看上去像:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id       | int(10) unsigned | NO   | MUL | NULL    |                |
| submission_id | int(11)          | NO   | MUL | NULL    |                |
| comment       | text             | NO   |     | NULL    |                |
| parent_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| created       | datetime         | NO   | MUL | NULL    |                |
| created_ip    | int(11)          | NO   |     | NULL    |                |
| helpful_count | int(11)          | NO   | MUL | NULL    |                |
| deleted       | tinyint(4)       | NO   | MUL | 0       |                |
+---------------+------------------+------+-----+---------+----------------+'

和看起来像的用户:

  +----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                   | Null | Key | Default           | Extra                       |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id             | int(10) unsigned       | NO   | PRI | NULL              | auto_increment              |
| email          | varchar(128)           | NO   | MUL | NULL              |                             |
| username       | varchar(23)            | NO   |     | NULL              |                             |
| name           | varchar(32)            | NO   |     | NULL              |                             |
| about          | varchar(255)           | NO   |     | NULL              |                             |
+----------------+------------------------+------+-----+-------------------+-----------------------------+

我使用的 GROUP_CONCATLEFT JOIN ,给我所有的子元素评论 (内我 submissions_comments 表) 与此查询:

SELECT parent.id, MAX(parent.comment) as pcomm,
       GROUP_CONCAT(child.id ORDER BY child.id) as children,
       GROUP_CONCAT(child.comment ORDER BY child.id) as childrenComments
FROM   submissions_comments AS parent
 LEFT  JOIN submissions_comments AS child
   ON  child.parent_id = parent.id
WHERE  parent.parent_id IS NULL
GROUP  BY parent.id
ORDER  BY parent.id;

给我:

+----+-------------------------------+----------+--------------------------------------------------------+
| id | pcomm                         | siblings | siblingComments                                        |
+----+-------------------------------+----------+--------------------------------------------------------+
|  1 | This is a parent              | 2,4      | This is a child comment,This is a second child comment |
|  3 | I don't have any children | NULL     | NULL                                                   |
|  5 | Testing one two three         | NULL     | NULL                                                   |
|  6 | adsdfsasdf                    | NULL     | NULL                                                   |
|  7 | asdfadsfdsaf                  | NULL     | NULL                                                   |
|  8 | asdfasdsadfsadf               | NULL     | NULL                                                   |
|  9 | asdfsdafsdafdaafds            | NULL     | NULL                                                   |
+----+-------------------------------+----------+--------------------------------------------------------+

我的问题:我想加入 users usubmissions_comments 可以让 usernameemailnameabout 为每个父注释和子元素的意见。

我的查询:

SELECT parent.id, MAX(parent.comment) as pcomm,
       parent.username, 
       GROUP_CONCAT(child.id ORDER BY child.id) as children,
       GROUP_CONCAT(child.comment ORDER BY child.id) as childrenComments,
       GROUP_CONCAT(child.username ORDER BY child.id) as childrenUsernames
FROM   submissions_comments AS parent
 LEFT  JOIN submissions_comments AS child
   ON  child.parent_id = parent.id
LEFT JOIN users u on parent.user_id = u.id
GROUP  BY parent.id
ORDER  BY parent.id;

我想要:

+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+
| id | pcomm                         | children | childrenComments                                       |childrenUsernames      |
+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+
|  1 | This is a parent              | 2,4      | This is a child comment,This is a second child comment |blahbster, user123     |
|  3 | I don't have any children | NULL     | NULL                                                   |                       |
|  5 | Testing one two three         | NULL     | NULL                                                   |                       |
|  6 | adsdfsasdf                    | NULL     | NULL                                                   |                       |
|  7 | asdfadsfdsaf                  | NULL     | NULL                                                   |                       |
|  8 | asdfasdsadfsadf               | NULL     | NULL                                                   |                       |
|  9 | asdfsdafsdafdaafds            | NULL     | NULL                                                   |                       |
+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+

我不断收到这些错误: 错误 1054 (42S22): 未知的列 'parent.username' 在字段列表和错误 1054 (42S22): 未知的列 'child.username' 在字段列表中。

解决方法 1:

如果你需要用户名为两个 parentchild ,你要去找他们; 他们不是在 submissions_comments 表中,但宁愿链接到那张桌子。

SELECT parent.id, MAX(parent.comment) as pcomm,
       pu.username, 

       /* display child comments in order of child USER id */
       GROUP_CONCAT(cu.id ORDER BY cu.id) as children,
       GROUP_CONCAT(child.comment ORDER BY cu.id) as childrenComments,
       GROUP_CONCAT(cu.username ORDER BY cu.id) as childrenUsernames

  /* handle parent submissions, with user identity */
  FROM submissions_comments AS parent
  LEFT JOIN users AS pu ON parent.user_id = u.id   /* get parents' user info */

  /* handle child submissions, with user identity */
  LEFT JOIN submissions_comments AS child  ON  child.parent_id = parent.id
  LEFT JOIN users AS cu ON child.user_id = cu.id   /* get children's user info */

GROUP  BY parent.id, pu.username   /* avoid stupid nonstandard MySQL GROUP BY */
ORDER  BY parent.id, pu.username

正如你所看到的有几个调整到您的查询来获得你所需。

官方微信
官方QQ群
31647020