Join 3 tables with 2 right joins

[d22]

I wrote a database for a Little League to track registrations. I am working to combine 3 tables to make one so I can populate a MS Word form. The tables are Player, Parent_1 and Parent_2. The Player_ID is the PK in the Player table and the FK in the other 2. The problem I have is my query works but if a player doesn't have a parent in either parent table, then the record won't return a value. I can add a RIGHT JOIN to either of the tables and it will then return null vaules for the table with the JOIN. The problem is it won't let me put a RIGHT JOIN on both of the parent tables.

Here is my code:
SELECT tblPlayer.Last_Name, tblPlayer.First_Name, tblPlayer.Age, tblPlayer.DOB, tblPlayer.Sex, tblPlayer.Address, tblPlayer.City, tblPlayer.Zip, tblPlayer.Phone, tblPlayer.School, tblRosterParent1.tblParent1_Last_Name, tblRosterParent1.tblParent1_First_Name, tblRosterParent1.home_phone, tblRosterParent1.[Parent1 email], tblParent2.Last_Name, tblParent2.First_Name, tblParent2.address, tblMed.Med_ID_Number, tblMed.Med_Provider, tblMed.Med_Conditions
FROM tblParent2 RIGHT JOIN ((tblPlayer INNER JOIN tblRosterParent1 ON tblPlayer.Player_ID=tblRosterParent1.Player_ID) INNER JOIN tblMed ON (tblPlayer.Player_ID=tblMed.Player_ID) AND (tblRosterParent1.Player_ID=tblMed.Player_ID)) ON tblParent2.ID=tblPlayer.Parent_ID2;

 

[PHV]

And what about this FROM clause ?
FROM ((tblPlayer
LEFT JOIN tblMed ON tblPlayer.Player_ID=tblMed.Player_ID)
LEFT JOIN tblRosterParent1 ON tblPlayer.Player_ID=tblRosterParent1.Player_ID)
LEFT JOIN tblParent2 ON tblPlayer.Parent_ID2=tblParent2.ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[d22]

That worked great. Thank you. D.