MYSQL Query help
Posted: Sun Jul 24, 2011 8:13 pm
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.
student
[text]
student_ID {PK}
first_name
last_name
email
family_ID {FK}
family
family_ID {PK}
family_phone
adult
adult_ID {PK}
first_name
last_name
family_ID {FK}
[/text]
I've worked out query
The only problem with this is I get two rows for the kids who have two parents listed in the database. Like this.
[text]
| 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 |
[/text]
I was going to display the results using this code but can't figure out how to get the adult's data to display as one line, ie. Stan and Karen Smith.
I'm hoping this is posted in the right category. I was assuming the problem is with my SQL query. Any help would be great.
-J
student
[text]
student_ID {PK}
first_name
last_name
family_ID {FK}
family
family_ID {PK}
family_phone
adult
adult_ID {PK}
first_name
last_name
family_ID {FK}
[/text]
I've worked out query
Code: Select all
SELECT student.first_name,
student.last_name,
student.email,
adult.first_name,
adult.last_name,
family.family_phone
FROM family
LEFT JOIN student on family.family_ID = student.family_ID
LEFT JOIN adult on family.family_ID = adult.family_ID
[text]
| 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 |
[/text]
I was going to display the results using this code but can't figure out how to get the adult's data to display as one line, ie. Stan and Karen Smith.
Code: Select all
$num=mysql_numrows($roster_result);
$i = 0;
while ($i < $num) {
$student-first=mysql_result($roster_result,$i,"student.first_name");
$student-last=mysql_result($roster_result,$i,"student.last_name");
$student-mobile=mysql_result($roster_result,$i,"student.mobile_phone");
$student-email=mysql_result($roster_result,$i,"student.email");
$family-phone=mysql_result($roster_result,$i,"family.family_phone");
$adult-first=mysql_result($roster_result,$i,"student.mobile_phone");
$adult-last=mysql_result($roster_result,$i,"studentemail");
echo "<tr>";
echo "<td>";
echo $student-last.", ".$student-first;
echo "</td>";
echo "<td>";
echo $student-mobile;
echo "</td>";
echo "<td>";
echo $student-email;
echo "</td>;
echo "<td>";
echo $family-mobile;
echo "</td>";
echo "<td>";
echo $adult-first.", ".$adult-last;
echo "</td>"</tr>";
$i++;
-J