need to weed out duplicate record in database search

[pdmiller]

I'm displaying records of parent and child information for a school based on whether the family has children enrolled in a class for 2009. Some families have more than one child enrolled in a class for 2009. So when the results are displayed the families with more than one child enrolled in a class for 2009 are displayed more than once. I only need the family displayed once.

Here is my code:
$sql = "SELECT * FROM parent_units, class_units WHERE parent_units.parent_id=class_units.parent_id AND class_units.class_year = 2009 AND class_units.withdrew!='1' AND class_units.completed!='1' ORDER BY work_parent_last_name ASC";

Is there a way I can add something to eliminate the duplicate records that come up with the search? Is there a way to find the parent_id and if that parent_id is listed more than once in the search result, I can tell it to only display it once?

[laserlight]

I admit I have some difficulty in constructing queries when I do not know quite what I am selecting :o

But this would be solved either by the use of DISTINCT or GROUP BY, but I am not sure which with the given information.

You can either wait for someone more skilled at SQL to give you the answer, or you can provide more information. For example, instead of a SELECT *, select exactly those columns that you need. Then tell us what is the relevant database schema.

[pdmiller]

Here are the fields I'm trying to access and display:

$sql = "SELECT parent_units.parent_id, parent_units.work_parent_first_name, parent_units.work_parent_last_name, parent_units.other_parent_first_name, parent_units.other_parent_last_name, parent_units.street, parent_units.city, parent_units.state, parent_units.zip, parent_units.phone_A_area, parent_units.phone_A, parent_units.phone_B_area, parent_units.phone_B, parent_units.wp_email, parent_units.op_email, class_units.class_year, class_units.withdrew, class_units.completed, class_units.parent_id FROM parent_units, class_units WHERE parent_units.parent_id=class_units.parent_id AND class_units.class_year = 2009 AND class_units.withdrew!='1' AND class_units.completed!='1' ORDER BY work_parent_last_name ASC";

Does that help?

[pdmiller]

It's going off the parent_id field in both tables. I need the parent_id field in the class_units table to be DISTINCT.

[beefy23]

you've got 2 options

a) select distinct class_units.parents_id, <other fields> from . . . where . . .

b) select <all fields you need> where . . . group by class_units.parents_id

give either a whirl and see how you get on