dongya6997
dongya6997
2019-05-19 01:49

如何在mysql中进行查询选择父和子而不重复父行

已采纳

I would like to obtain the table data and the data of the children are in another table but I do not know how to make the query.

what I know how to do is a normal query select * form table 1, I know how to perform "inner join" but I do not want the data in table 1 "parent" repeated

select * form parent INNER JOIN childs on parent.pnt_id=childs.id_pnt;

parent childs

+-----+----------+------+-------+   +-----+----------+-------+  
| id  |   pnt_id | info |infotwo|   | id  |   id_pnt |n_child|...  
+-----+----------+------+-------+   +-----+----------+-------+  
|    1|         5| home | big   |   |    5|         1|  joan | 
+-----+----------+------+-------+   +-----+----------+-------+  
|    2|         3| work | fat   |   |    3|         1|  luci |  
+-----+----------+------+-------+   +-----+----------+-------+  
|    3|         0| soft |  thin |   |    6|         2|  troy |  
+-----+----------+------+-------+   +-----+----------+-------+

I would like a query in the form of

$parents = arrar (
          info -> "home",
          infotwo -> "big",
          data_child -> arrar (
                      n_child -> "joan",
                      n_child -> "luci"
                     )
)
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duanlan5320 duanlan5320 2年前

    I think you want aggregation:

    select p.info, p.infotwo, group_concat(n_child) as children
    from parent p join
         childs c
         on p.pnt_id = c.id_pnt
    where p.pnt_id = 1
    group by p.info, p.infotwo;
    
    点赞 评论 复制链接分享