Jump to content


Photo

Multiple JOINS not working


Best Answer Psycho, 28 February 2013 - 06:16 PM

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

Go to the full post


  • Please log in to reply
3 replies to this topic

#1 jcanker

jcanker

    Advanced Member

  • Members
  • PipPipPip
  • 249 posts

Posted 27 February 2013 - 11:20 PM

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.

 

 

 


The main thing I've learned from PHPFreaks:
foreach($ThreadReplyAuthors as $key=>$val){
      if($val == "Pikachu2000" || $val == "Thorpe" || $val == "Psycho"){
      thankMyLuckyStars();     }
}

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,130 posts
  • LocationCheshire, UK

Posted 28 February 2013 - 06:01 PM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,709 posts
  • LocationCanada

Posted 28 February 2013 - 06:16 PM   Best Answer

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 by Psycho, 28 February 2013 - 06:17 PM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#4 jcanker

jcanker

    Advanced Member

  • Members
  • PipPipPip
  • 249 posts

Posted 02 March 2013 - 09:24 AM

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


The main thing I've learned from PHPFreaks:
foreach($ThreadReplyAuthors as $key=>$val){
      if($val == "Pikachu2000" || $val == "Thorpe" || $val == "Psycho"){
      thankMyLuckyStars();     }
}




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com