Need help with MySQL query

I'm trying to formulate the proper SQL query to pull a roster up on this database I was asked to produce for my kids' HS band. Arrghh. Any help would be great. I have 3 tables.

[U]student[/U]
student_ID {PK}
first_name
last_name
email
family_ID {FK}

[U]family[/U]
family_ID {PK}
family_phone

[U]adult[/U]
adult_ID {PK}
first_name
last_name
family_ID {FK}

Code (markup):

I've worked out query

$roster_query="SELECT s.first_name, s.last_name, s.email, s.mobile_phone, f.family_phone, a.first_name, a.last_name
FROM student AS s JOIN family AS f ON s.family_ID = f.family_ID
JOIN adult AS a ON a.family_ID = s.family_ID";

PHP:

The only problem with this is I get two rows for the kids who have two parents listed in the database. Like this.

| first_name | last_name   | email  | first_name | last_name  | family_phone |
+------------+-------------+-----------------------------------+------------+---
-------------+--------------+
| Spencer    | Smith     | spencer@gmail.com               | Stan       | Smith        | 805-555-1212 |
| Spencer    | Smith     | spencer@gmail.com               | Karen      | Smith        | 805-555-1212 |
| Katherine  | Jones   | katherine@hotmail.com       | Wes        | Broderick      | 805-555-1313 |

Code (markup):

I was going to display the results using something like this code but can't figure out how to get the adult's data to display as one line, ie. Stan and Karen Smith.

echo "<table>";
echo "<tr>";
echo "<td>Student Name</td>";
echo "<td>Student Mobile</td>";
echo "<td>Student Email</td>";
echo "<td>Adult Name</td>";
echo "<td>Adult Mobile</td>";
echo "</tr>";

while($row = mysql_fetch_array($sql))
{

  echo "<tr>";
  echo "<td>" . $row["s.last_name"] . ", " . $row["s.first_name"] . "</td>";
  echo "<td>" . $row["s.mobile_phone"] . "</td>";
  echo "<td>" . $row["s.email"] . "</td>";
  echo "<td>" . $row["a.last_name"] . ", " . $row["a.first_name"] . "</td>";
  echo "<td>" . $row["f.family_phone"] . "</td>";
  echo "</tr>";

}

echo "</table>";

PHP:

I'm hoping this is posted in the right category. I was assuming the problem is with my SQL query and not how it is presented in PHP. My apologies if I'm incorrect. Any help would be great.

 

maybe the following code help you

GROUP BY `s.email`

Code (markup):

 

That just eliminates the second parent from the list all together. I appreciate the suggestion though.

 

SELECT GROUP_CONCAT(a.first_name
SEPARATOR ' & '), a.last_name
FROM adult AS a
GROUP BY last_name;

I think this is what I wanted. Thanks.

 

Finished query with student information for anyone coming to this thread later.

SELECT s.first_name, s.last_name, s.email, s.mobile_phone,
f.family_phone, GROUP_CONCAT(a.first_name SEPARATOR ' & '), a.last_name

FROM student AS s
JOIN family AS f ON s.family_ID = f.family_ID
JOIN adult AS a ON a.family_ID = s.family_ID
GROUP BY a.family_ID;