Jump to content

Eliminate duplicates in nested Join


jcanker

Recommended Posts

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 by jcanker
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.