[RESOLVED] Help With SQL Query

[scriderpsd]

I have two tables, "providers" (containing about 1400 rows) and "students" (containing about 2200 rows) joined together by the key student_id. Not all students have a provider that they are tied to. Essentially I want to pull some fields from my students table for all of the 2200 records and also pull the prov_name from the providers table. So my result set will contain 2200 records but only 1400 will have a prov_name. The rest should have a null value in that field.

So, my question is how do I join the two tables together to get it to output all 2200 students from the students table and also give me the prov_name from the providers table. If they don't have a record in the providers table, then a null value should be given for that student in the prov_name field.

If I do a simple join on the student_id (i.e. WHERE students.student_id=providers.student_id) I end up with just the 1400 row result set.

Please help.

Thanks,

Scott

[mjax]

Use a LEFT JOIN.

[devinemke]

$result = mysql_query('SELECT student_id, prov_name FROM providers') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$providers[$row['student_id']] = $row['prov_name'];}

$result = mysql_query('SELECT * FROM students') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	echo 'name: ' . $row['student_name'];
	if (isset($providers[$row['student_id']])) {echo '<br>provider: ' . $providers[$row['student_id']];}
	echo '<br>';
}