Need help with MySQL query

[joshuasanders]

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}

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";

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     | [e-mail]               | Stan       | Smith        | 805-555-1212 |
| Spencer    | Smith     | [e-mail]               | Karen      | Smith        | 805-555-1212 |
| Katherine  | Jones   | [e-mail]       | Wes        | Broderick      | 805-555-1313 |

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>";

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.

[mwasif]

Use GROUP BY.

[joshuasanders]

I tried GROUP BY with a number of different fields and all I get is it just eliminates the second parent from the list all together. I appreciate the suggestion though.

[Ezzaral]

You could use group_concat() and group by student id.

[joshuasanders]

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.

[Ezzaral]

I don't think you want to just group by last name though. Different families could have the same last name. I would stick to family id.

[joshuasanders]

I don't think you want to just group by last name though. Different families could have the same last name. I would stick to family id.

You the man! Agreed.

[joshuasanders]

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;