Multiple JOINS not working

[jcanker]

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

 

 

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.

 

 

 

[Barand]

 

SELECT ... FROM A INNER JOIN B ON ...

 

returns results only where there are matching records in both tables

(JOIN is same as INNER JOIN)

 

 

 

SELECT ... FROM A LEFT JOIN B ON ...

 

returns all records from A with matching data from B where it exists

[Psycho]

Let me rephrase your question another way. I think you want a list of all users who meet one of two criteria:

1. They have a child registered for the current season

OR

2. They are listed in the leaders table.

 

So why not create a simple query against the users table using a clause in your WHERE statement for matching users IDs for those two conditions:

 

SELECT users.userID, users.fName, users.lName
FROM users
WHERE users.userID IN
        (SELECT parents.parentID
         FROM parents
         JOIN registrations ON parents.childID = registrations.userID
         WHERE registrations.seasonID =:seasonID

         UNION
        
         SELECT userID
         FROM leaders)
  AND users.fName != '' && users.lName like :lName

 

Not tested, but it should be close

Edited February 28, 2013 by Psycho

[jcanker]

Outstanding.  I *really* appreciate the assistance, both of you.  Sorry it took me a few to respond--real life has kept me away from this project the last few days.

 

I tried Psyco's solution first--phpmyadmin is returning the expected rows when I change the parameterized :lName with a wildcard search like the autocompleter sends via AJAX.  It also only returns a name once, even if they have multiple rows in TABLE parents because they have more than one son in the program.

 

You guys rock.  Wish I knew the SQL side as well as you do.  Maybe one day soon at the rate I'm going, but I'm playing catch up