Working on a project for my son's baseball program.
There are several tables involved with my problem:
TABLE:users in which every parent or player is a separate row with a unique userID that is used throughout the rest of the application
TABLE:registrations in which every player registered is listed by userID in his own row
TABLE: parents in which every parent->player relationship is tracked, one relationship per row parentID is parent's userID, childID is the child's userID
TABLE: leaders in which adults filling leadership roles such as coach or age group commissioner is attached to the ID of the position by userID.
Trying to get a return of all the adults in the program for use in an autocompleter. I'm able to grab a list that gets all the registered players, finds their parents' userIDs, then gets the parents' names from TABLE:users.
$query = "SELECT DISTINCT users.userID, users.fName, users.lName
(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";
The problem is that I have figured out that we have a few adults who volunteer with the program although their children are no longer playing in the program. The Autocompleter can't find them because they don't have children registered, so they aren't in the search.
I need to match up userID from TABLE leaders as well to also include those few adults who are volunteering but don't have children registered (or the one funky case where his ex-wife has herself and her new husband listed on the registration as the parents, leaving the volunteer off the registration, even though he's the one who's coaching the team!)
My QUESTION IS: I have tried several ways of adding in another layer to the JOIN statement but haven't gotten it to work yet. How do I:
Get all the userIDs of the registered players, THEN
Get the userID of their parents from TABLE parents THEN
Add in the userIDs of anyone in TABLE leaders THEN
Get their first and last name only once (no repeats for those parents who are listed multiple times in TABLE parents because they have 2 or more children registered)? The query above works great except for those who don't have kids playing this year.