Eliminate duplicates in nested Join

[jcanker]

Helping my son's baseball program track their player, parent, and team information a bit better.

 

MySQL 5.5, PHP 5.4.9

 

My dilemma involves three tables:

TABLE: users which tracks the userID, first name, last name, etc for every user (both parent and player)

TABLE: registrations which utilizes the child's userID within the registration data. Every registration is entered into this table, including the player's userID and the seasonID he is registered for.

TABLE: parents which tracks the relationship between every child and their parents. Every child->parent relationship is a row in this table.

 

In order to get a JSON list of all the parents of registered players (to use in jQuery autocomplete for selecting adults as coach/assist coach, when defining a team) I'm using the following query:

$query = "SELECT users.userID, users.fName, users.lName
FROM
(SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12
JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName";
$stmt=$conn->prepare($query);
$stmt->execute(Array(
 ':seasonID' => $_SESSION['seasonID'],
 ':lName' => $name
 ));
$result = $stmt->fetchALL(PDO::FETCH_ASSOC);
$json=json_encode($result);
print_r($json);

 

This works like a charm, EXCEPT if a parent has two or more children registered in the program. By design, they exist:

in TABLE:users once with a unique userID

not at all in TABLE:registrations

in TABLE: parents one for each child they have registered with the program

 

The problem is that for the parents with multiple children registered, the parent's name is being returned for every child they have registered. This means they show up twice (or more) in the JSON used to build the autocomplete list.

 

My Question: Is there a way to construct this query to eliminate additional instances of the parent? Or do I just have to step through the results, build a new array, check to see if the parent is already there before choosing to add/not add to the array, then run the json_encode on that new array?

Edited February 24, 2013 by jcanker

[Barand]

does this work

 

SELECT DISTINCT users.userID, users.fName, users.lName ...

[jcanker]

I'll try that and report back. In the meantime I have it working via PHP, which isn't my preference:

$query = "SELECT users.userID, users.fName, users.lName
FROM
(SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12
JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName";
$stmt=$conn->prepare($query);
$stmt->execute(Array(
   ':seasonID' => $_SESSION['seasonID'],
  ':lName' => $name
  ));
$resultArray = Array();
$i = 0;
while($row=$stmt->fetch())
{
if(!empty($resultArray))
{
//reset the "Add this record" tripwire
 $add = 1;
 foreach($resultArray as $outer)
 {
  if(in_array($row['userID'],$outer)){$add=0;}
 }//end foreach
 if($add == 1)
 {
 //add them in
  $resultArray[$i]['userID']=$row['userID'];
  $resultArray[$i]['fName']=$row['fName'];
  $resultArray[$i]['lName']=$row['lName'];
 }
}//if(!empty)
else
{
//add them in
 $resultArray[$i]['userID']=$row['userID'];
 $resultArray[$i]['fName']=$row['fName'];
 $resultArray[$i]['lName']=$row['lName'];
}
$i++;
}
$json=json_encode($resultArray,JSON_PRETTY_PRINT);
echo "<pre>";print_r($json);

[jcanker]

Schweet!

Nice, elegant SQL solution to my issue. Thank you so much. I'll mark this as solved.

The code now reads:

$query = "SELECT DISTINCT users.userID, users.fName, users.lName
FROM
(SELECT parents.parentID FROM parents JOIN registrations ON parents.childID = registrations.userID WHERE registrations.seasonID =:seasonID) AS t12
JOIN users ON t12.parentID = users.userID WHERE users.fName != '' && users.lName like :lName";
$stmt=$conn->prepare($query);
$stmt->execute(Array(
 ':seasonID' => $_SESSION['seasonID'],
 ':lName' => $name
 ));
$result = $stmt->fetchALL(PDO::FETCH_ASSOC);
$json=json_encode($result,JSON_PRETTY_PRINT);
echo "<pre>";
print_r($json);

and works without all that messy PHP code to dig down to the 2nd level.