仅显示一次数据

Question

I have created a contactlist for parents in a daycare center. Now I wish to display it on the webpage. So far, it technically works perfect but it's the layout that I'm not happy with.

Short on how my database works: Each parent can have one or more children and each child can have one or more parents - a many to many relationship. I have broken it down to three tables - parents, children and parent_children in order to make a one to many relationship. Then I have used JOIN and everything displays nicely. However, since it show every entity it gets rather cluttered to display it on an webpage. I'll explain with an example:

Family 1: Mark and Alice Wilson have two children, John and Bill.
Family 2: Peter and Jessica Robertson have one child, Lisa.

Current layout:
Parent                Child
-------------------------------
Mark Wilson           John Wilson
Mark Wilson           Bill Wilson
Alice Wilson          John Wilson
Alice Wilson          Bill Wilson

Peter Robertson       Lisa Robertson
Jessica Robertson     Lisa Robertson
-------------------------------

Desired layout:
Parent                Child
-------------------------------
Mark Wilson           John Wilson
Alice Wilson          Bill Wilson

Peter Robertson       Lisa Robertson
Jessica Robertson
-------------------------------

Is there any good way to get the desired layout?

---

Ok, so now I've come so far as to get this working in ONE column, depending on how I use GROUP_CONCAT:

Code:

SELECT GROUP_CONCAT(parents.name) AS Parents, children.name
FROM parents p
LEFT JOIN parents_children pc USING(id_parent)
LEFT JOIN children c USING(id_child)
GROUP BY pc.id_parent;

Result:

Parents                             Children
-----------------------------------------------
Mark Wilson, Alice Wilson           Bill Wilson
Mark Wilson, Alice Wilson           John Wilson
Peter Robertson, Jessica Robertson  Lisa Robertson

Likewise, if I GROUP_CONCAT children.name and GROUP BY pc.id_child instead I get:

Parents                Children
------------------------------------------
Mark Wilson            Bill Wilson, John Wilson
Alice Wilson           Bill Wilson, John Wilson
Peter Robertson        Lisa Robertson
Jessica Robertson      Lisa Robertson

I really want a combo of these, with the following result:

Parents                                Children
----------------------------------------------------
Mark Wilson, Alice Wilson              Bill Wilson, John Wilson
Peter Robertson, Jessica Robertson     Lisa Robertson

Answer 1

SELECT
    Parents
  , GROUP_CONCAT(name ORDER BY name) AS Children
FROM
  ( SELECT 
        GROUP_CONCAT(p.id_parent ORDER BY p.name) AS parents_ids
      , GROUP_CONCAT(p.name ORDER BY p.name) AS Parents
      , pc.id_child
      , c.name
    FROM parents p 
      JOIN parents_children pc USING(id_parent)
      JOIN children c USING(id_child)
    GROUP BY pc.id_child
  ) AS tmp 
GROUP BY parents_ids ;

You can test it in: SQL-Fiddle